.Any() on collection navigations after projection can cause client-side evaluation

[csboling]

In some cases using Where clauses of the form .Where(x => x.Collection.Any(...)) or
.Where(x => x.Collection.Where(...).Any()) can cause client-side evaluation when the Where clause follows .Select. It seems that presence or absence of .ToList() inside .Select(...) makes a difference here when projecting to a DTO with an IEnumerable collection property.

Steps to reproduce

Here is a project which configures client-side evaluation as an exception, causing some Xunit test cases to fail.

A query like

dbContext.Set<Library>()
    .Where(l => l.Books.Any(b => b.Title == "The Cat in the Hat"))
    .Select(l => new LibraryDto { . . . })

is translated to SQL as expected (WHERE EXISTS . . .). However, when the projection comes first,

dbContext.Set<Library>()
    .Select(l => new LibraryDto { . . . })
    .Where(l => l.Books.Any(b => b.Title == "The Cat in the Hat"))    

cannot be translated. When .ToList() is not present in the .Select(...), a query like

dbContext.Set<Library>()
    .Select(l => new LibraryDto { . . . })
    .Where(l => l.Books.Any())

can be translated, but this is evaluated client-side when .Any() contains a comparison on the collection items or when ToList() is used for the collection navigation property.

Not sure if this is a duplicate issue or a limitation of projection when dealing with correlated subqueries.

Further technical details

EF Core version: 2.1.1
Database Provider: Sqlite in the test project, but affects other providers as well.
Operating system: Windows 10
IDE: Visual Studio 2017 15.7.5, .NET Core SDK 2.1.302.

[divega]

@csboling thanks for raising this.

Projecting into a type that is neither an anonymous type nor a mapped type inside the query does trigger client evaluation. This is a known limitation tracked at #8107.

If you still think that ToList() being present makes a difference, could you please provide some examples? Otherwise I would like to treat this issue as a duplicate of #8107.

[csboling]

In the reproduction project there are the following test cases:

// passes
[Fact]
public void WhereAny_AfterProjectionWithoutToList_WithoutComparison()
{
    var dbSet = this.DbContext.Set<Library>();
    var query = this.ProjectWithoutToList(dbSet)
        .Where(l => l.Books.Any());
    query.ToList();
}

// fails:
// Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning:
// The LINQ expression 'Any()' could not be translated and will be evaluated locally.'.
[Fact]
public void WhereAny_AfterProjection_WithoutComparison()
{
    var dbSet = this.DbContext.Set<Library>();
    var query = this.Project(dbSet)
        .Where(l => l.Books.Any());
    query.ToList();
}

where the projection methods being used are:

public IQueryable<LibraryDto> Project(IQueryable<Library> queryable) =>
    queryable
        .Select(l => new LibraryDto
        {
            Id = l.Id,
            City = new CityDto
            {
                Id = l.City.Id,
                Name = l.City.Name,
            },
            Books = l.Books
                .Select(b => new BookDto
                {
                    Id = b.Id,
                    Title = b.Title,
                })
                .ToList(),
        });

public IQueryable<LibraryDto> ProjectWithoutToList(IQueryable<Library> queryable) =>
    queryable
        .Select(l => new LibraryDto
        {
            Id = l.Id,
            City = new CityDto
            {
                Id = l.City.Id,
                Name = l.City.Name,
            },
            Books = l.Books
                .Select(b => new BookDto
                {
                    Id = b.Id,
                    Title = b.Title,
                }),
        });

and the relevant view model is:

public class LibraryDto : BaseDto
{
    public CityDto City { get; set; }

    public IEnumerable<BookDto> Books { get; set; }
}

However I believe .ToList() may be necessary here to avoid N+1 queries by telling EF it can buffer the collection navigation result? Interestingly this test case, which differs from the passing one by using the .Any overload that accepts a predicate, also fails, and the clause it dumps is interesting:

// Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning:
// The LINQ expression 
// 'where {from BookDto b in {
//   from Book b in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Issues.OpsAfterProjection.Book]) 
//   where  ?= (Property([l], "Id") == Property([b], "LibraryId")) =? 
//   select new BookDto() {Id = [b].Id, Title = [b].Title}} 
//     where ([b].Title == "The Cat in the Hat")
//     select [b] => Any()
// }'
// could not be translated and will be evaluated locally.'.
[Fact]
public void WhereAny_AfterProjectionWithoutToList()
{
    var dbSet = this.DbContext.Set<Library>();
    var query = this.ProjectWithoutToList(dbSet)
        .Where(l => l.Books.Any(b => b.Title == "The Cat in the Hat"));
    query.ToList();
}

For this predicate, unlike .Any(), projecting with ToList() is also evaluated client-side for basically exactly the same reason (differs from the last one only by a .ToList() after select new BookDto . . .

Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning:
The LINQ expression 
'where {from BookDto b in {
  from Book b in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Issues.OpsAfterProjection.Book]) 
  where  ?= (Property([l], "Id") == Property([b], "LibraryId")) =?
  select new BookDto() {Id = [b].Id, Title = [b].Title}}.ToList() 
    where ([b].Title == "The Cat in the Hat") 
    select [b] => Any()
}'
could not be translated and will be evaluated locally.'.

I'm interested to experiment some to see if using anonymous types can provide a workaround for me. In reality I'm using AutoMapper's Queryable extensions to build the projection expression (which includes .ToList()). For the most part .Where, .OrderBy etc seem to work fine in this situation when they reference properties on one-to-one navigation properties, but this is the typical situation where I want to reference a collection navigation.

[csboling]

I've added a few more test cases which show the same behaviors for anonymous types.

[ajcvickers]

Triage: we are going to reconsider these behaviors for 3.0, so added a link to #12795 to track with other 3.0 query-related issues.

[smitpatel]

Duplicate of #8107