Filter a nested collections with LINQ "Include"

Question

I searched online for a long time, but I couldn't really get a straight answer to this. How would I use Include to load a nested collection using Where to filter it? Let's say, for instance, that I want to include all the CartItems that are not disabled:

var myCart = _dbContext.Carts
                 .Include(cart => cart.CartShippingBoxes
                     .Select(cartShippingBox => cartShippingBox.CartItems
                         .Where(cartItem => !cartItem.IsDisabled))); // This doesn't work

What about you try to apply the Where first. before all the includes. And you have two redundant includes in your code. Drop one of them. — PiJei, Aug 28 '18 at 11:37
I am wondering why you want to filter it in the `include`. In SQL terms it is like having a sub query within the `FROM` clause which doesn't make sense. — Mustafa Shujaie, Aug 28 '18 at 11:46
Why would I return a huge collection when I only need a small part of it? — tocqueville, Aug 28 '18 at 11:48
Do you want to do this? https://learn.microsoft.com/en-us/ef/ef6/querying/related-data#applying-filters-when-explicitly-loading-related-entities — Mustafa Shujaie, Aug 28 '18 at 12:12
Also in the same link it is mentioned that it is not possible to filter includes — Mustafa Shujaie, Aug 28 '18 at 12:14

Hossein · Answer 1 · 2018-08-29T03:34:11.570

2

Try this:

var carts = _dbContext.CartShippingBoxes
                 .Include(item => item.Cart)
                 // .ThenInclude(cart => cart.A)
                         .Where(cartItem => !cartItem.IsDisabled))
                  Select(s=> s.Cart).ToList();

Or use overload of Include that takes parameter of type string like:

.Include("Cart").Include("Cart.CollectionA").Include("Cart.CollectionB")
.Include("Cart.CollectionZ")

edited Aug 29 '18 at 03:34

answered Aug 28 '18 at 11:38

Hossein

3,083
3
16
33

This approach doesn't work for me in production, I need to start from `_dbContext.Carts` because the query is actually much more complicated and has various `Include` related to other objects and collection of the `Cart` itself – tocqueville Aug 28 '18 at 11:49
@tocqueville You can include want you want with `ThenInclude` after `Include(item => item.Cart)` . – Hossein Aug 28 '18 at 11:52
@tocqueville You also can use overload of `Include` that takes parameter of type `string` . – Hossein Aug 28 '18 at 12:01
I'm using EF6, I cannot use ThenInclude – tocqueville Aug 28 '18 at 12:37
@tocqueville Intellisense will only give you the `Include(string path)` version of the method. – Hossein Aug 28 '18 at 14:49

Filter a nested collections with LINQ "Include"

1 Answers1