QueryRewrite: support contains on collection navigation

[maumar]

query:

customers.Select(c => c.Orders.Contains(someOrder))

currently Enumerable.Contains is translated to IN, which is incorrect for the case above

[ajcvickers]

@maumar Why is the translation to IN incorrect?

[divega]

@ajcvickers, @smitpatel I tried this to try to answer the question above, and a query like this throws before generating any SQL using nightly builds. Is this a known issue:

System.InvalidOperationException
  HResult=0x80131509
  Message=Operation is not valid due to the current state of the object.
  Source=Microsoft.EntityFrameworkCore.Relational
  StackTrace:
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSubquery(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Repro14173.Program.Main(String[] args) in C:\Users\divega\source\repos\Repro14173\Program.cs:line 21

[smitpatel]

It should have been rewritten by #15939

Can you write PK based equality directly and see if it translates?

[divega]

@smitpatel yes, it does translate:

SELECT CASE
    WHEN @__someThing_Id_0 IN (
        SELECT [t].[Id]
        FROM [Things] AS [t]
        WHERE ([p].[Id] = [t].[PersonId]) AND [t].[PersonId] IS NOT NULL
    )
     THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END
FROM [People] AS [p]

Repro:

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

namespace Repro15554
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new MyContext())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();
                var someThing = new Thing { Id = 1 };

                var query1 = context.People
                    .Select(p => p.Things.Contains(someThing))
                    .ToList(); // fails

                var query2 = context.People
                    .Select(p => p.Things.Select(t => t.Id).Contains(someThing.Id))
                    .ToList(); // works
            }
        }
    }

    public class MyContext : DbContext
    {
        public DbSet<Person> People { get; set; }

        public DbSet<Thing> Things { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlServer(@"server=(localdb)\mssqllocaldb; database=parameternames; integrated security=true; connectretrycount=0")
                .AddInterceptors(new LoggingInterceptor(Console.WriteLine));
        }
    }

    public class Thing
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public ICollection<Thing> Things { get; set; } = new List<Thing>();
    }

    public class LoggingInterceptor : DbCommandInterceptor
    {
        private readonly Action<string> _loggingAction;
        public LoggingInterceptor(Action<string> loggingAction)
        {
            _loggingAction = loggingAction;
        }
        public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
        {
            _loggingAction(command.CommandText);
            return base.ReaderExecuting(command, eventData, result);
        }
    }
}

[smitpatel]

So now the questions:

• Is translation of second query above incorrect?
  • If yes, then why? and what should we translate it to?
  • If no, then first query should be converted to second by entity equality rewrite.

[divega]

The translation looks correct to me 😄

[maumar]

Contains on keys seems correct to me also. I don't remember what I meant by my initial comment, perhaps we tried to create IN directly on entity objects, rather than keys. Either way, I agree with @smitpatel and @divega here