Introduction
I come across a situation in which I had a navigation property that had a property with a collection of items and I needed to use this data to loop over within a React component. The problem was, I was using a .Select() within LINQ to format my JSON response to a more suitable format that I can easily interpret on my frontend. This then caused the .Include() to “not work” and fail to populate the property.
The Solution
The solution was pretty simple, although I’m still wondering if there is a better way (please comment if you have one). All I did was include the following after my .Include()‘s and before my .Select();
|
1 |
.ToAsyncEnumerable() |
Apparently this causes the query to get executed and then the rest of the operations after will be done in memory. This then populated my navigation property collection property and I was able to use the data on the frontend.
Example code
Before
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
var categoryData = await _context.ProductCategories .Where(c => c.Slug == categorySlug) .Include(c => c.Products) .Include(s => s.SubCategories) .ThenInclude(p => p.Products) .Select(c => new { categoryId = c.ProductCategoryId, title = c.Title, slug = c.Slug, subCategories = c.SubCategories, items = c.Products }).FirstOrDefaultAsync(); |
After
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
var categoryData = await _context.ProductCategories .Where(c => c.Slug == categorySlug) .Include(c => c.Products) .Include(s => s.SubCategories) .ThenInclude(p => p.Products) .ToAsyncEnumerable() // <----------- ADD THIS .Select(c => new { categoryId = c.ProductCategoryId, title = c.Title, slug = c.Slug, subCategories = c.SubCategories, items = c.Products }).FirstOrDefault(); |
I hope this helped you out if you’re wondering why your properties aren’t being populated when you use an .Include() and a .Select()!
Just understand that when you use Include, ef will select ALL the fields in that table, which is not very optimized. It’s usually preferable to just put the specific items you need in the select (even from sub-tables) and you won’t need any include to begin with.
Also: you usually would want the filtering to take place in the back-end and not in memory as the databases are more optimized for searches.
Thanks for your reply. I’ll have a go at that as it’s probably something I didn’t think of when implementing. Thanks!