Efcore: Async Query on Many-To-Many with Projection and Where error & N+1 Queries

[maganuk]

There are three issues with the following query:

1. Doing a CountAsync() causes the query to fail, whereas Count() works

2. Doing Count() creates N+1 queries

3. If doing Count():
   Where(x => x.Title.Contains("united")) - has 1 result
   Where(x => x.Title.Contains("United")) - has 1 result
   Where(x => x.CountryStates.Contains("Florida")) - has 1 result
   Where(x => x.CountryStates.Contains("florida")) - has no results

Seems like a bug.

Exception message: "Expression of type 'System.Collections.Generic.IAsyncEnumerable`1[System.String]' cannot be used for parameter of type 'System.Collections.Generic.IEnumerable`1[System.String]' of method 'System.Collections.Generic.List`1[System.String] ToList[String](System.Collections.Generic.IEnumerable`1[System.String])'
Parameter name: arg0"
Stack trace: "   at System.Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arguments, ParameterInfo pi, String methodParamName, String argumentParamName, Int32 index)
   at System.Linq.Expressions.Expression.Call(MethodInfo method, Expression arg0)
   at System.Linq.Expressions.MethodCallExpression1.Rewrite(Expression instance, IReadOnlyList`1 args)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.ReplaceClauseReferences(Expression expression, IQuerySource querySource, Boolean inProjection)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.CompileMainFromClauseExpression(MainFromClause mainFromClause, QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitMainFromClause(MainFromClause fromClause, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalEntityQueryableExpressionVisitor.VisitSubQuery(SubQueryExpression expression)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.ReplaceClauseReferences(Expression expression, IQuerySource querySource, Boolean inProjection)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitWhereClause(WhereClause whereClause, QueryModel queryModel, Int32 index)
   at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateAsyncQueryExecutor[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.CountAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at ConsoleApp1.Program.<MainAsync>d__1.MoveNext() in C:\\Sources\\Playground\\NetCore.M2MTest\\NetCore.M2MTest\\Program.cs:line 36
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at ConsoleApp1.Program.Main(String[] args) in C:\\Sources\\Playground\\NetCore.M2MTest\\NetCore.M2MTest\\Program.cs:line 15"

Steps to reproduce

```c#
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 CountryStates
{
    public int CountryID { get; set; }
    public int StateID { get; set; }
    public Country Country { get; set; }
    public State State { get; set; }
}

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

var country1 = new Country { Title = "United States" };
var country2 = new Country { Title = "India" };
var c1State1 = new State { Title = "Florida" };
var c1State2 = new State { Title = "California" };
var c2State1 = new State { Title = "Haryana" };
var c2State2 = new State { Title = "Maharashtra" };
_context.CountryStates.Add(new CountryStates { Country = country1, State = c1State1 });
_context.CountryStates.Add(new CountryStates { Country = country1, State = c1State2 });
_context.CountryStates.Add(new CountryStates { Country = country2, State = c2State1 });
_context.CountryStates.Add(new CountryStates { Country = country2, State = c2State2 });
_context.SaveChanges();

var result = await _context.Countries.Select(x => new CountryDTO {
      ID = x.ID,
      Title = x.Title,
      CountryStates = x.CountryStates.Select(y => y.State.Title).ToList()
}).Where(x => x.CountryStates.Contains("Florida")).CountAsync();

```

Further technical details

EF Core version: 2.1.0-preview2-final
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.7-Preview

[smitpatel]

This is the query model

      Compiling query model:
      '(from Country x in DbSet<Country>
      where
          (from string <generated>_1 in List<string> ToList(
                  from CountryStates y in [x].CountryStates
                  select [y].State.Title)
          select [<generated>_1]).Contains("florida")
      select new CountryDTO{
          ID = [x].ID,
          Title = [x].Title,
          CountryStates = List<string> ToList(
                  from CountryStates y in [x].CountryStates
                  select [y].State.Title)
      }
      ).Count()'

1. CountAsync should not throw exception. It should give correct result even with client eval version. (Hence this is bug)`

2. Doing Count() causes N+1 queries because you are doing Contains on a list. There is no way to represent the ToList() call on server. Hence we have to call ToList client side. That means, we have to form the list for each Country and evaluate where on it. Hence N + 1 queries executed.

3. When you do Contains on the list, we have to do client eval. So the Contains become case sensitive. When you do it on Title it is a string property so we can translate it to server. SqlServer is by default do case insensitive string comparison that is the reason for mis-match in result.

Work-around:
If you use IEnumerable instead of List (essentially remove ToList() call and modify your DTO object then, we actually can translate the CountryStates.Contains to server using ... IN ( subquery ). It also works with Async.
If you don't want to modify the DTO then write the Where clause before Select like this.
```C#
var result = db.Countries
.Where(x => x.CountryStates.Select(y => y.State.Title).Contains("Florida"))
.Select(x =>
new CountryDTO
{
ID = x.ID,
Title = x.Title,
CountryStates = x.CountryStates.Select(y => y.State.Title).ToList()
}).Count();

Generates SQL
```SQL
      SELECT COUNT(*)
      FROM [Countries] AS [x]
      WHERE N'Florida' IN (
          SELECT [y.State].[Title]
          FROM [CountryStates] AS [y]
          INNER JOIN [States] AS [y.State] ON [y].[StateID] = [y.State].[ID]
          WHERE [x].[ID] = [y].[CountryID]
      )

Also if you are doing just Count then Select is unnecessary.

[maganuk]

Actually I was using projectTo queryable extensions from Automapper instead of a manual projection and it was still case sensitive.

I Will try to change the countrystates to an IEnumerable and see if that helps.

Regarding issue 2, I should mention here that I am currently trying to port a fairly complex rest api to ef core, and the same query does not produce N+1 Queries in EF6. Again, relevant to mention that I’m using automapper’s projectTo queryable extension.

Any chance issue 1 will be fixed with 2.1.0?

Thanks

[smitpatel]

@maganuk - Given that this did not work in 2.0.2 & we are already very close to release of 2.1 this will not be fixed in 2.1. We will fix it in future release though.

[ajcvickers]

@smitpatel Typo in your previous comment? Missing "not"? (Changes the meaning of the message quite a bit!)

[smitpatel]

Updated. 🤦‍♂️

[maganuk]

@smitpatel i've tried changing the DTO's CountryStates Property to an IEnumerable and removed the ToList(). It seems to work fine, however N+1 queries are still being generated. Is that expected behavior? Any workarounds?

[ajcvickers]

Triage: moving this to 2.2 for the CountAsync issue, and added a note to #11748 about other potential impacts of ToList.

[smitpatel]

Blocked by #19194

[smitpatel]

SQL generated in 1 & 2

      SELECT COUNT(*)
      FROM [Countries] AS [c]
      WHERE EXISTS (
          SELECT 1
          FROM [CountryStates] AS [c0]
          INNER JOIN [State] AS [s] ON [c0].[StateID] = [s].[ID]
          WHERE ([c].[ID] = [c0].[CountryID]) AND ([s].[Title] = N'Florida'))

For 3 since Title is matched exactly (we are doing contains over a list of string values),
United gives 0 results.
Florida gives 1 result.
Casing does not matter for default scenario as database are case insensitive.