AsNoTracking() with OrderBy() causes N+1 or Deadlock

[JakobFerdinand]

When using AsNoTracking() before OrderBy() the ToList() causes a N+1 Query and ToListAsync() ends in a deadlock.

var query = (from a in context.Authors
                            select new
                            {
                                a.Name,
                                Books = a.Books.Select(b => b.Title).ToList()
                            })
                  /* --> */ .AsNoTracking()
                            .OrderBy(a => a.Name);
query.ToList();               // N+1 Problem
await query.ToListAsync();    // Deadlock

SELECT [a0].[Name], [a0].[Id]
FROM [Authors] AS [a0]

exec sp_executesql N'SELECT [b0].[Title]
FROM [Books] AS [b0]
WHERE @_outer_Id1 = [b0].[AuthorId]',N'@_outer_Id1 int',@_outer_Id1=895

exec sp_executesql N'SELECT [b0].[Title]
FROM [Books] AS [b0]
WHERE @_outer_Id1 = [b0].[AuthorId]',N'@_outer_Id1 int',@_outer_Id1=257

exec sp_executesql N'SELECT [b0].[Title]
FROM [Books] AS [b0]
WHERE @_outer_Id1 = [b0].[AuthorId]',N'@_outer_Id1 int',@_outer_Id1=890

...

When adding AsNoTracking() after the OrderBy() statement everything works like expected (2 queries).

var query = (from a in context.Authors
                            select new
                            {
                                a.Name,
                                Books = a.Books.Select(b => b.Title).ToList()
                            })
                            .OrderBy(a => a.Name)
                  /* --> */ .AsNoTracking();
query.ToList();
await query.ToListAsync(); 

SELECT [a].[Name], [a].[Id]
FROM [Authors] AS [a]
ORDER BY [a].[Name], [a].[Id]

SELECT [t].[Name], [t].[Id], [a.Books].[Title], [a.Books].[AuthorId]
FROM [Books] AS [a.Books]
INNER JOIN (
    SELECT [a0].[Name], [a0].[Id]
    FROM [Authors] AS [a0]
) AS [t] ON [a.Books].[AuthorId] = [t].[Id]
ORDER BY [t].[Name], [t].[Id]

Maybe relevant to #4007

Further technical details

EF Core version: 2.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 1803
IDE: Visual Studio 2017 15.7.3

[maumar]

related to #6611

[maumar]

Problem here is that AsNoTracking makes the query model more complicated - relinq introduces subquery every time it encounteres a result operator (e.g. AsNoTracking, Include, Distinct etc).

AsNoTracking gets eliminated from the query model, but because there is orderby afterwards we don't collapse it back to the simple form.

So the QM looks like this:

from <>f__AnonymousType0<string, List<string>> a in 
    from Author a in DbSet<Author>
    select new { 
        [a].Name, 
        List<string> ToList(
            from Book b in DbSet<Book>
            where  ?= (Nullable<int>)Property([a], "Id") == Property([b], "AuthorId") =? 
            select [b].Title)
     }
order by [a].Name asc
select [a]

rather than

from Author a in DbSet<Author>
order by [a].Name asc
select new { 
    [a].Name, 
    List<string> ToList(
        from Book b in DbSet<Book>
        where  ?= (Nullable<int>)Property([a], "Id") == Property([b], "AuthorId") =? 
        select [b].Title)
 }

N+1 optimization only gets applied on the outermost projection, which in this case is [a], so the relevant subquery is not being found and optimized.

In this particular case we could optimize the QM, move the orderby to the inner subquery and eliminate the outer, but the translation could be a bit tricky.

[maumar]

@JakobFerdinand for the best results we recommend to use AsNoTracking / Include operators directly after DbSet, like so:

from a in ctx.Authors.AsNoTracking()
select ...

[maumar]

Separately, we should investigate the async case for deadlock - TaskLiftingExpressionVisitor might not be catching this scenario

[JakobFerdinand]

@maumar The Problem with using AsNoTracking() directly on the DbSet is that we have some cases where we need to reuse a query.

Something like:

IQueryable<Author> GetAuthorsWithBooks()
    => from a in context.Authors
       select new
       {
           a.Name,
           Books = a.Books.Select(b => b.Title).ToList()
       };

// Service A:
GetAuthorsWithBooks().AsNoTracking().ToList();

// Service B:
GetAuthorsWithBooks().ToList();

Of cause we could use something like:

IQueryable<Author> GetAuthorsWithBooks(bool asNoTracking = false)
    => from a in (asNoTracking ? context.Authors.AsNoTracking() : context.Authors)
       select new
       {
           a.Name,
           Books = a.Books.Select(b => b.Title).ToList()
       };

But we think it´s a bit tideous - specialy after we got used to the great fluent API for all our queries.

[maumar]

@JakobFerdinand fair enough. If you can re-structure your code so that AsNoTracking is applied as last operation (like what you already did in the second query), that produces queries that are easy to translate also.

[ajcvickers]

Triage:

• @maumar to file a separate issue for the deadlock part of this; we will triage it separately
• @smitpatel to find/file the issue around the optimization
• This issue becomes an issue to reduce the impact of AsNoTracking by removing it as a result operator from the expression tree before Relinq gets to it.

[smitpatel]

Works on 3.1