How to filter nested collection Entity Framework objects?

by

You can’t do that directly in a “neat” way, but you have a few options.
First of all, you can explicitly load the child collection after you’ve fetched the stores. See the Applying filters when explicitly loading related entities section.

If you don’t want to make extra trips to the database, you will have to construct your own query and project the parent collection and the filtered child collections onto another object manually. See the following questions for examples:
Linq To Entities – how to filter on child entities
LINQ Query – how sort and filter on eager fetch

Edit

By the way, your first .Where(rcu=>rcu.Orders.Select(cu=>cu.Customer.Deleted==false)) attempt doesn’t work since this way you are applying a filter to your parent collection (stores) rather than the nested collection (e.g. all the stores that don’t have deleted customers).
Logically, the code filtering the nested collection should be placed in the Include method. Currently, Include only supports a Select statement, but personally I think it’s time for the EF team to implement something like:

.Include(cu => cu.Orders.Select(c => c.Customers.Where(cust => !cust.IsDeleted)));

More Related Contents:

  1. Entity Framework: There is already an open DataReader associated with this Command
  2. Linq: adding conditions to the where clause conditionally
  3. Entity framework linq query Include() multiple children entities
  4. LINQ to Entities does not recognize the method ‘System.String Format(System.String, System.Object, System.Object)’
  5. Only initializers, entity members, and entity navigation properties are supported
  6. Why is .Contains slow? Most efficient way to get multiple entities by primary key?
  7. Convert String to Int in EF 4.0
  8. Linq-to-entities – Include() method not loading
  9. Entity Framework – “Unable to create a constant value of type ‘Closure type’…” error
  10. Instantiating a context in LINQ to Entities
  11. conditional include in linq to entities?
  12. EF 4: Removing child object from collection does not delete it – why?
  13. Linq To Entities – how to filter on child entities
  14. Entity Framework, Code First and Full Text Search
  15. LINQ OrderBy not ordering .. changing nothing .. why?
  16. Simple Linq query has duplicated join against same table?
  17. ‘Contains()’ workaround using Linq to Entities?
  18. The relationship could not be changed because one or more of the foreign-key properties is non-nullable
  19. How to use a string to create a EF order by expression?
  20. Why is inserting entities in EF 4.1 so slow compared to ObjectContext?
  21. ADO.NET DbContext Generator vs. ADO.NET Poco Entity Generator (ObjectContext)
  22. LINQ Where Ignore Accentuation and Case
  23. LINQ with SQLite (linqtosql)
  24. Dynamic table name in linq [duplicate]
  25. How do you ensure Cascade Delete is enabled on a table relationship in EF Code first?
  26. Join and Include in Entity Framework
  27. Creating dynamic queries with entity framework
  28. EF Code First DBContext and Transactions
  29. How to group by DateTime.Date in EntityFramework
  30. Model First with DbContext, Fails to initialize new DataBase

Leave a Comment