EF Core: Fetching A List of Entities with Their Children

Question

I have Post and PostImage entities. A Post entity can have a list of PostImage entities (i.e., one-to-many). I want to fetch a list of all posts and include all of its list of images. So I wrote the following piece of code:

var posts = _appDataContext.Posts .Select(x => new { x.Id, x.Title, x.Body, Images = x.Images.Select(y => new { y.Id }) });

The code is all executed in the database which is what I want, but here's the catch. From the console log, it appears that EF is first fetching the list of posts, and then it loops over them to fetch their corresponding images (extra queries + extra fetching time). Is there any other way to fetch the data all at once (posts + their images). Both posts and images have extra columns, that's why I used the Select statement; to filter out the columns that I don't need. I tried using Include, but nothing has changed.

P.S. I'm using EntityFramework Core.

Ivan Stoev · Accepted Answer · 2019-08-31 22:50:52Z

At once (single SQL query) - no. Because this is how EF Core queries work. The minimum is one SQL query for the main data + 1 SQL query for each collection. For your case the minimum is 2 SQL queries. Still much better than N + 1 queries issue you are experiencing currently.

The solution is to use EF Core 2.1+ which has Optimization of correlated subqueries. Also as mentioned in the documentation link, you have to opt-in for that optimization by "including ToList() in the right place(s)":

var posts = _appDataContext.Posts .Select(x => new { x.Id, x.Title, x.Body, Images = x.Images.Select(y => new { y.Id }).ToList() // <-- });

Collectives™ on Stack Overflow

EF Core: Fetching A List of Entities with Their Children

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related