Query: [Nav Prop Translation] EF can't join between same types if one of them is queryed inside the expression

[Suchiman]

Sorry for the bad title, i have no idea how to describe it.
Try running following Code

using Microsoft.Data.Entity;
using System.Collections.Generic;
using System.Linq;

namespace EFTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new TestContext())
            {
                Post postOfUserTest = context.Posts
                    .FirstOrDefault(post => post.User == context.Names
                        .FirstOrDefault(name => name.Text == "Test").User);
            }
        }
    }

    public class TestContext : DbContext
    {
        public DbSet<Name> Names { get; set; }
        public DbSet<User> Users { get; set; }
        public DbSet<Post> Posts { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Data Source=db.sqlite");
        }
    }

    public class Name
    {
        public int NameId { get; set; }
        public string Text { get; set; }
        public User User { get; set; }
    }

    public class User
    {
        public int UserId { get; set; }
        public List<Name> Names { get; set; }
    }

    public class Post
    {
        public int PostId { get; set; }
        public User User { get; set; }
    }
}

It will crash with

Unhandled System.InvalidOperationException.
  HResult=-2146233079
  Message=The binary operator Equal is not defined between type "System.Nullable`1[System.Int32]" and "EFTest.User".
  Source=System.Core
  StackTrace:
       at System.Linq.Expressions.Expression.GetEqualityComparisonOperator(ExpressionType binaryType, String opName, Expression left, Expression right, Boolean liftToNull)
       at System.Linq.Expressions.Expression.Equal(Expression left, Expression right, Boolean liftToNull, MethodInfo method)
       at System.Linq.Expressions.Expression.MakeBinary(ExpressionType binaryType, Expression left, Expression right, Boolean liftToNull, MethodInfo method, LambdaExpression conversion)
       at System.Linq.Expressions.Expression.MakeBinary(ExpressionType binaryType, Expression left, Expression right, Boolean liftToNull, MethodInfo method)
       at Microsoft.Data.Entity.Query.ExpressionVisitors.NavigationRewritingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression) in EntityFramework\src\EntityFramework.Core\Query\ExpressionVisitors\NavigationRewritingExpressionVisitor.cs:Zeile 208.
       at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
       at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
       at Remotion.Linq.Clauses.WhereClause.TransformExpressions(Func`2 transformation)
       at Remotion.Linq.QueryModel.TransformExpressions(Func`2 transformation)
       at Microsoft.Data.Entity.Query.ExpressionVisitors.NavigationRewritingExpressionVisitor.Rewrite(QueryModel queryModel) in EntityFramework\src\EntityFramework.Core\Query\ExpressionVisitors\NavigationRewritingExpressionVisitor.cs:Zeile 94.
       at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.OptimizeQueryModel(QueryModel queryModel) in EntityFramework\src\EntityFramework.Core\Query\EntityQueryModelVisitor.cs:Zeile 217.
       at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel) in EntityFramework\src\EntityFramework.Core\Query\EntityQueryModelVisitor.cs:Zeile 145.
       at Microsoft.Data.Entity.Storage.Database.CompileQuery[TResult](QueryModel queryModel) in EntityFramework\src\EntityFramework.Core\Storage\Database.cs:Zeile 34.
       at Microsoft.Data.Entity.Query.QueryCompiler.<>c__DisplayClass16_0`1.<CompileQuery>b__0() in EntityFramework\src\EntityFramework.Core\Query\QueryCompiler.cs:Zeile 121.
       at Microsoft.Data.Entity.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler) in EntityFramework\src\EntityFramework.Core\Query\Internal\CompiledQueryCache.cs:Zeile 32.
       at Microsoft.Data.Entity.Query.QueryCompiler.CompileQuery[TResult](Expression query) in EntityFramework\src\EntityFramework.Core\Query\QueryCompiler.cs:Zeile 112.
       at Microsoft.Data.Entity.Query.QueryCompiler.Execute[TResult](Expression query) in EntityFramework\src\EntityFramework.Core\Query\QueryCompiler.cs:Zeile 69.
       at Microsoft.Data.Entity.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) in EntityFramework\src\EntityFramework.Core\Query\Internal\EntityQueryProvider.cs:Zeile 37.
       at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)
       at EFTest.Program.Main(String[] args) in EFTest\EFTest\Program.cs:Zeile 13.
  InnerException: 

[divega]

@Suchiman Thanks for reporting this. What version exactly are you using?

[Suchiman]

Built from 2553a0c but no custom modifications

[rowanmiller]

This is a dupe of #113
EF doesn't currently support evaluating equality of an entity (post.User == context.Names.FirstOrDefault(name => name.Text == "Test").User) - you currently need to test equality of the primary keys.

[Suchiman]

@rowanmiller
you currently need to test equality of the primary keys.
This delivers wrong results:

x.User.Id == context.Nicknames.FirstOrDefault(n => n.Name == nickname).User.Id

results in:

SELECT "post"."PostId", "post"."UserUserId", "post.User"."UserId"
FROM "Post" AS "post"
INNER JOIN "User" AS "post.User" ON "post"."UserUserId" = "post.User"."UserId"

The filter is lost.

Also i wanted to point out that EF6 didn't support comparison of entities in queries to local instances but against other entities in the same queries as per my sample code.
The generated SQL in EF6 for post.User == context.Names.FirstOrDefault(name => name.Text == "Test").User looks like:

SELECT 
1 AS "C1", 
"Extent1"."PostId" AS "PostId",
"Extent1"."User_UserId" AS "User_UserId"
FROM   "Posts" AS "Extent1"
LEFT OUTER JOIN  (SELECT 
    "Extent2"."User_UserId" AS "User_UserId"
    FROM "Names" AS "Extent2"
    WHERE ("Extent2"."Text" = @p__linq__0) OR (("Extent2"."Text" IS NULL) AND (@p__linq__0 IS NULL)) LIMIT 1 ) AS "Project1" ON 1 = 1
LEFT OUTER JOIN "Users" AS "Extent3" ON "Project1"."User_UserId" = "Extent3"."UserId"
WHERE ("Extent1"."User_UserId" = "Extent3"."UserId") OR (("Extent1"."User_UserId" IS NULL) AND ("Extent3"."UserId" IS NULL))

[rowanmiller]

Need to investigate the dropping of the filter mentioned in above comment

[maumar]

Reassigning to @anpete since he already started working on the general issue

[rowanmiller]

@mikary can you let us know what you find, then we can re-triage

[mikary]

Ran the original repro and it now throws a NullArgumentException in query compilation NavigationRewritingExpressionVisitor.RewriteNavigationProperties. @maumar noted this issue is potentially related to #5191, but looks like a more complex case. The exception / call stack is the same when using key comparisons

using (var context = new TestContext())
{
    Post postOfUserTest = context.Posts
        .FirstOrDefault(post => post.User.UserId == context.Names
            .FirstOrDefault(name => name.Text == "Test").User.UserId);
}

[maumar]

works in current bits, closing

[Suchiman]

Well 😐 ... works enough to work but not enough to be useful in any way. Loading the entire table into memory to find a single user is... not webscale 😛

2016-07-20 00:15:43 [Warning] The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
2016-07-20 00:15:43 [Warning] The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
2016-07-20 00:15:43 [Warning] The LINQ expression 'from User post.User in {[post.User_group] => DefaultIfEmpty()}' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
2016-07-20 00:15:43 [Warning] The LINQ expression '(IIF(([post.User] != null), Property([post.User], "UserId"), null) == Property({from Name name in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[EFTest.Name]) join User name.User in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[EFTest.User]) on IIF(([name] != null), Property([name], "UserId"), null) equals Convert(Property([name.User], "UserId")) into IEnumerable`1 name.User_group from User name.User in {[name.User_group] => DefaultIfEmpty()} where ([name].Text == "Test") select [name.User] => FirstOrDefault()}, "UserId"))' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
2016-07-20 00:15:43 [Warning] The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
2016-07-20 00:15:43 [Warning] The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
2016-07-20 00:15:43 [Warning] The LINQ expression 'from User name.User in {[name.User_group] => DefaultIfEmpty()}' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
2016-07-20 00:15:43 [Warning] The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
2016-07-20 00:15:43 [Warning] The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
2016-07-20 00:15:43 [Information] Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
PRAGMA foreign_keys=ON;

2016-07-20 00:15:43 [Information] Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

SELECT "post"."PostId", "post"."UserId", "post.User"."UserId"
FROM "Posts" AS "post"
LEFT JOIN "Users" AS "post.User" ON "post"."UserId" = "post.User"."UserId"
ORDER BY "post"."UserId"

2016-07-20 00:15:43 [Information] Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

SELECT "name"."NameId", "name"."Text", "name"."UserId", "name.User"."UserId"
FROM "Names" AS "name"
LEFT JOIN "Users" AS "name.User" ON "name"."UserId" = "name.User"."UserId"
WHERE "name"."Text" = 'Test'
ORDER BY "name"."UserId"

Is this the most inefficient SQL contest?... doing a left join which doesn't constrains the result set and without selecting from the join and then doing a second query with exactly the reverse statement, only cross apply could make it worse.

[rowanmiller]

@maumar is there already another issue tracking the client eval aspect of this?

[maumar]

@rowanmiller @Suchiman yes, we track it here: #4588