Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

N+1 Queries #8531

Closed
maganuk opened this issue May 20, 2017 · 12 comments

Comments

@maganuk
Copy link

commented May 20, 2017

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

This comment has been minimized.

Copy link
Member

commented May 22, 2017

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

@maganuk

This comment has been minimized.

Copy link
Author

commented May 22, 2017

@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

This comment has been minimized.

Copy link
Author

commented May 22, 2017

@maumar

This comment has been minimized.

Copy link
Contributor

commented Jun 27, 2017

@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

This comment has been minimized.

Copy link
Author

commented Jun 27, 2017

@maumar

This comment has been minimized.

Copy link
Contributor

commented Jun 27, 2017

@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

This comment has been minimized.

Copy link
Author

commented Jun 27, 2017

@smasherprog

This comment has been minimized.

Copy link

commented Jun 4, 2018

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

This comment has been minimized.

Copy link
Contributor

commented Jun 4, 2018

@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

This comment has been minimized.

Copy link

commented Jun 4, 2018

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

This comment has been minimized.

Copy link

commented Jun 5, 2018

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

This comment has been minimized.

Copy link
Contributor

commented Jun 6, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
4 participants
You can’t perform that action at this time.