CODEWITHSUNDEEP
c#entity-framework
JsonStatham
JsonStatham

Reputation: 10364

Entity Framework filtering list within object

I'm trying to filter a collection within an object in Entity Framework. I followed this example, which makes sense.

This is my resulting query:

var filteredClientEmp = context.Clients.Include(x => x.CompanyEmployee)
                .Where(c => c.HrPersonId == paId && c.CompanyEmployee.Any(e => e.EmployeeBirthday != null && e.EmpType == 2 &&
                                                                                    e.LeftCompany == null))
                .Select(c => new
                {
                    c,
                    CompanyEmployee =
                    c.CompanyEmployee.Where(e => e.EmployeeBirthday != null && e.EmpType == 2 &&
                                                 e.LeftCompany == null)
                })
                .ToList()
                .Select(pro => pro.c)
                .ToList();

            return filteredClientEmp;

However, when I inspect the filteredClientEmp object, it contains employee's with no birthday and records where the left company value is not equal to null.

The Client object has a non virtual list of Employee:

 public List<Employee> CompanyEmployee { get; set; }

Why is this filtering not working?

Upvotes: 0

Views: 1430

Answers (2)

CodeCaster
CodeCaster

Reputation: 151594

Include() unconditionally loads all child entities. Because you project into an anonymous type with two properties:

  • c: the client with all its employees
  • CompanyEmployee: the employees for that client to whom your conditions apply

And then continue to project only c, this c still includes all employees. You need to overwrite c's CompanyEmployee collection with the filtered collection:

.Select(p => { p.c.CompanyEmployee = p.CompanyEmployee; return p.c; })

Upvotes: 2

SBFrancies
SBFrancies

Reputation: 4240

Your problem is in:

.Select(pro => pro.c).ToList();

You are not returning the clients with the list of employees filtered in:

CompanyEmployee = c.CompanyEmployee.Where(e => e.EmployeeBirthday != null && e.EmpType == 2 && e.LeftCompany == null)

In fact that property if the anonymous type is not used at all. Instead you are returning the filtered list of all clients which:

1) Have the specified HrPersonId and

2) Have at least one employee with a birthday, an employee type of 2 and have not left the company.

To return the Clients with the filtered list your final Select should look something like:

.Select(pro => { pro.c.CompanyEmployee = pro.CompanyEmployee; return pro.c; })

Upvotes: 1

Related Questions