EF Core: Fetching A List of Entities with Their Children

c# ef-core-2.0 entity-framework entity-framework-core

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.

1
2
8/31/2019 9:51:06 PM

Accepted Answer

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() // <--
    });
2
8/31/2019 10:50:52 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow