N+1 Queries

[maganuk]

Using EF Core, the following code should generate a single query to get all the countries along with all the IDs for the states in each country. In EF6 this works as expected. However, EF Core is generating a single query to get all the countries and then generating a query for each country to get the states id.

Steps to reproduce

public class Country
    {
        public int ID { get; set; }
        public string Title { get; set; }

        public ICollection<CountryStates> CountryStates { get; set; }
    }

public class State
    {
        public int ID { get; set; }
        public string Title { get; set; }
        
        public ICollection<CountryStates> CountryStates { get; set; }
    }

public class CountryDTO
    {
        public int ID { get; set; }
        public string Title { get; set; }
        public List<int> CountryStates { get; set; }
    }

Using Automapper:

    cfg.CreateMap<Country, CountryDTO>();

    cfg.CreateMap<CountryStates, int>()
        .ProjectUsing(c=>c.StateID);

I tired both:

    _context.Countries.ProjectTo<CountryDTO>().ToList();

and

    _context.Countries.Include(x=>x.CountryStates).ProjectTo<CountryDTO>().ToList();

Further technical details

EF Core version: 2.0.0-Preview1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 Update 3 (Preview)

[ajcvickers]

@maganuk Can you post the SQL that gets generated for all the queries?

[maganuk]

@ajcvickers

SELECT [y].[ID], [y].[Title]
FROM [Countries] AS [y]

Then multiple:

SELECT [x].[StateID]
FROM [CountryStates] AS [x]
WHERE @_outer_ID = [x].[CountryID]

[maganuk]

@ajcvickers I created a Gist for this:

https://gist.github.com/maganuk/3745e509971fda1b5bb8f4b53d6a510b

[maumar]

@maganuk it's a duplicate of #4007, EF doesn't know how to efficiently translate queries with collection navigations in projection, like automapper produces here.

As a workaround you would have to fetch all the data beforehand and apply the projection into DTO on the client.

This produces 2 queries, regardless of the number of countries in the database:

SELECT [c].[ID], [c].[Title]
FROM [Countries] AS [c]
ORDER BY [c].[ID]

SELECT [c.CountryStates].[CountryID], [c.CountryStates].[StateID]
FROM [CountryStates] AS [c.CountryStates]
INNER JOIN (
    SELECT [c0].[ID]
    FROM [Countries] AS [c0]
) AS [t] ON [c.CountryStates].[CountryID] = [t].[ID]
ORDER BY [t].[ID]

Alternatively, you can write the entire query by hand, this way you can apply filters and custom projections to the initial query, so that EF doesn't pull unnecessary data:

                var query = from c in ctx.Countries
                            join cs in ctx.CountryStates on c.ID equals cs.CountryID into grouping
                            from cs in grouping.DefaultIfEmpty()
                            select new { Country = c, StateID = cs.StateID };

                var result = query.ToList()
                    .GroupBy(k => k.Country, e => e.StateID)
                    .Select(g => new CountryDTO { ID = g.Key.ID, Title = g.Key.Title, CountryStates = g.Select(sid => sid).ToList() });

this produces the following, single SQL query (not that it doesn't fetch the CountryID from the CountryStates table):

SELECT [c].[ID], [c].[Title], [cs].[StateID]
FROM [Countries] AS [c]
LEFT JOIN [CountryStates] AS [cs] ON [c].[ID] = [cs].[CountryID]

[maganuk]

Thanks very much for the work around, but this isn't optimal considering that there may be millions of records which will first have to be fetched and then filtered through. We are also using linq2rest to apply odata filtering, sorting and paging. All of the above is done with a single query to the sql database in ef6, and the sql server returns the required number of records. Hope this can make its way to ef core, considering that it is common practice to fetch navigational collections, especially when developing a rest api. Best Regards On 27 Jun 2017 07:17, "Maurycy Markowski" <[email protected]> wrote: @maganuk <https://github.com/maganuk> it's a duplicate of #4007 <#4007>, EF doesn't know how to efficiently translate queries with collection navigations in projection, like automapper produces here. As a workaround you would have to fetch all the data beforehand and apply the projection into DTO on the client. This produces 2 queries, regardless of the number of countries in the database: SELECT [c].[ID], [c].[Title] FROM [Countries] AS [c] ORDER BY [c].[ID] SELECT [c.CountryStates].[CountryID], [c.CountryStates].[StateID] FROM [CountryStates] AS [c.CountryStates] INNER JOIN ( SELECT [c0].[ID] FROM [Countries] AS [c0] ) AS [t] ON [c.CountryStates].[CountryID] = [t].[ID] ORDER BY [t].[ID] Alternatively, you can write the entire query by hand, this way you can apply filters and custom projections to the initial query, so that EF doesn't pull unnecessary data: var query = from c in ctx.Countries join cs in ctx.CountryStates on c.ID equals cs.CountryID into grouping from cs in grouping.DefaultIfEmpty() select new { Country = c, StateID = cs.StateID }; var result = query.ToList() .GroupBy(k => k.Country, e => e.StateID) .Select(g => new CountryDTO { ID = g.Key.ID, Title = g.Key.Title, CountryStates = g.Select(sid => sid).ToList() }); this produces the following, single SQL query (not that it doesn't fetch the CountryID from the CountryStates table): SELECT [c].[ID], [c].[Title], [cs].[StateID]FROM [Countries] AS [c]LEFT JOIN [CountryStates] AS [cs] ON [c].[ID] = [cs].[CountryID] — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#8531 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/APKHFFaIx6Nz86VyH8iIH8TbCEprccOvks5sIF8tgaJpZM4NhOh7> .

[maumar]

@maganuk for the second workaround (the complex case) you can get custom filters and projections on both outer and inner collections:

                var query = from c in ctx.Countries
                            where c.Title == "United States"
                            join cs in ctx.CountryStates on c.ID equals cs.CountryID into grouping
                            from cs in grouping.Where(g => g.StateID != 10).DefaultIfEmpty()
                            select new { Country = c, StateID = cs.StateID };

                var result = query.ToList()
                    .GroupBy(k => k.Country, e => e.StateID)
                    .Select(g => new CountryDTO { ID = g.Key.ID, Title = g.Key.Title, CountryStates = g.Select(sid => sid).ToList() })
                    .ToList();

produces the following SQL:

SELECT [c].[ID], [c].[Title], [t].[StateID]
FROM [Countries] AS [c]
LEFT JOIN (
    SELECT [cs].*
    FROM [CountryStates] AS [cs]
    WHERE [cs].[StateID] <> 10
) AS [t] ON [c].[ID] = [t].[CountryID]
WHERE [c].[Title] = N'United States'

[maganuk]

In ef6 we're actually apending a queryable extension at the end of the linq statement for the filtering which generates the linq query based on the odata filter. I wonder if appending this to the query var will produce the same results. Will try and report back.

…

On Tue, 27 Jun 2017 at 11:56 PM, Maurycy Markowski ***@***.***> wrote: @maganuk <https://github.com/maganuk> for the second workaround (the complex case) you can get custom filters and projections on both outer and inner collections: var query = from c in ctx.Countries where c.Title == "United States" join cs in ctx.CountryStates on c.ID equals cs.CountryID into grouping from cs in grouping.Where(g => g.StateID != 10).DefaultIfEmpty() select new { Country = c, StateID = cs.StateID }; var result = query.ToList() .GroupBy(k => k.Country, e => e.StateID) .Select(g => new CountryDTO { ID = g.Key.ID, Title = g.Key.Title, CountryStates = g.Select(sid => sid).ToList() }) .ToList(); produces the following SQL: SELECT [c].[ID], [c].[Title], [t].[StateID]FROM [Countries] AS [c]LEFT JOIN ( SELECT [cs].* FROM [CountryStates] AS [cs] WHERE [cs].[StateID] <> 10 ) AS [t] ON [c].[ID] = [t].[CountryID]WHERE [c].[Title] = N'United States' — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#8531 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/APKHFPAEBJ1ljCfuqquZZj3AT7Elf1NSks5sIUk4gaJpZM4NhOh7> .

[smasherprog]

This is a problem for me as well. I am using OData and it is producing exactly what Maganuk is explaining. In EF6, this works fine, but ef core does not handle this case.

[maumar]

@smasherprog which version of EFCore are you using? In 2.1 we optimized a number of N+1 queries to produce only 2 queries instead. If you are using 2.1 and your query is not hindered by one of the limitations of the correlated collection optimization, can you create a new issue, posting your EF model and queries?

Here is the list of known limiations:

• doesn't work if the parent query results in a CROSS JOIN,
• doesn't work with result operators (i.e. Skip/Take/Distinct)
• doesn't work if outer query needs client evaluation anywhere outside projection (e.g. order by or filter by NonMapped property)
• doesn't work if inner query is correlated with query two (or more) levels up, (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name == c.Name).ToList()).ToList())
• doesn't work in nested scenarios where the outer collection is streaming (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name != "Foo").ToList())) - to make it work, outer collection must also be wrapped in ToList(). However it is OK to stream inner collection - in that case outer collection will take advantage of the optimization.

[smasherprog]

I just tested and found that this was the OData implementation not ef core. Sorry about that, ill repost this issue on the odata repo.

[smasherprog]

It also could be due to paging that we are using in most Odata queries in which we use top and skip. So, not sure whose issue this is now.

[maumar]

if you are using paging on the inner collection like so: customers.Select(c => c.Orders.Take(5).ToList() ) then it's most likely the problem. However, if the paging is on top level: customers.Select(c => c.Orders.ToList()).Take(5) then optimization should work and the problem is somewhere else