Entity Framework filtering list within object

Question

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?

Is the context clean (new)? And what EF version is this? — Ivan Stoev
– Ivan Stoev, Commented Jan 11, 2018 at 10:29
So probably this is ef-core and the Include is operational because the full Client is part of the projection. — Gert Arnold
– Gert Arnold, Commented Jan 11, 2018 at 10:46

CodeCaster · Accepted Answer · 2018-01-11 10:40:37Z

2

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; })

edited Jan 11, 2018 at 10:40

answered Jan 11, 2018 at 10:26

CodeCaster

153k24 gold badges237 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Ivan Stoev Over a year ago

This is what I thought initially. But Includes are normally ignored for projection queries, so there must be something else.

CodeCaster Over a year ago

@Ivan what do you mean? That c.CompanyEmployee should be null in the Select()?

Ivan Stoev Over a year ago

I mean (1) The Include should have no effect because of the projection (Select) (2) Assignment p.c.CompanyEmployee = ... normally should not be needed because the collection should already has been assigned and populated during the ToList call (by EF navigation property fixup). But it could help though :) The whole technique is a hack hence may fail for several reasons.

CodeCaster Over a year ago

@Ivan (1): (assuming EF6): unless lazy loading is enabled, you need to Include() entities that you actually want returned from the query. (2) The assignment is needed because the included collection needs to be filtered. Or am I missing something? Does EF Core do this differently? I haven't used it that intensively yet.

Gert Arnold Over a year ago

Whichever version it is, the Include should just be removed from the query. Relationship fixup will populate the filtered collections. Lazy loading isn't involved because the collection isn't virtual or because this is ef-core. Bit frustrating that OP doesn't seem to be stand-by after asking a question...

|

SBFrancies · Accepted Answer · 2018-01-11 10:40:30Z

1

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; })

answered Jan 11, 2018 at 10:40

SBFrancies

4,2602 gold badges18 silver badges50 bronze badges

Collectives™ on Stack Overflow

Entity Framework filtering list within object

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related