QueryTypes: binding error for query using defining query with navigation and query filter on top of that

[maumar]

Repro:

    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");

            using (var ctx = new MyContext())
            {
                ctx.Database.EnsureDeleted();
                ctx.Database.EnsureCreated();

                var f1 = new Faction { Name = "Skeliege" };
                var f2 = new Faction { Name = "Monsters" };
                var f3 = new Faction { Name = "Nilfgaard" };
                var f4 = new Faction { Name = "Northern Realms" };
                var f5 = new Faction { Name = "Scioia'tael" };

                var l11 = new Leader { Faction = f1, Name = "Bran Tuirseach" };
                var l12 = new Leader { Faction = f1, Name = "Crach an Craite" };
                var l13 = new Leader { Faction = f1, Name = "Eist Tuirseach" };
                var l14 = new Leader { Faction = f1, Name = "Harald the Cripple" };

                ctx.Factions.AddRange(f1, f2, f3, f4, f5);
                ctx.Leaders.AddRange(l11, l12, l13, l14);
                ctx.SaveChanges();
            }

            using (var ctx = new MyContext())
            {
                var q01 = ctx.Query<FactionQuery>().ToList();
            }
        }
    }

    public class MyContext : DbContext
    {
        public DbSet<Faction> Factions { get; set; }
        public DbSet<Leader> Leaders { get; set; }

        public DbQuery<LeaderQuery> LeadersQuery { get; set; }
        public DbQuery<FactionQuery> FactionsQuery { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=.;Database=QueryTypesRepro;Trusted_Connection=True;MultipleActiveResultSets=True");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Query<FactionQuery>().ToQuery(() => Set<Leader>().Where(lq => lq.Name != "Foo").Select(lq => new FactionQuery { Name = lq.Faction.Name  }));

            modelBuilder.Query<FactionQuery>().HasQueryFilter(fq => fq.Name.StartsWith("S"));
        }
    }

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

        public List<Leader> Leaders { get; set; }
    }

    public class FactionQuery
    {
        public string Name { get; set; }
    }

    public class Leader
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public Faction Faction { get; set; }
    }

    public class LeaderQuery
    {
        public string Name { get; set; }
    }

QM:

from FactionQuery fq in 
    from Leader lq in DbSet<Leader>
    join Faction lq.Faction in DbSet<Faction>
    on Property([lq], "FactionId") equals (Nullable<int>)Property([lq.Faction], "Id") into lq.Faction_group
    from Faction lq.Faction in 
        (from Faction lq.Faction_groupItem in [lq.Faction_group]
        select [lq.Faction_groupItem]).DefaultIfEmpty()
    where [lq].Name != "Foo"
    select new FactionQuery{ Name = [lq.Faction]?.Name }
where [fq]?.Name?.StartsWith("S") == True
select [fq]

exception:

Value cannot be null.
Parameter name: tableExpression

   at Microsoft.EntityFrameworkCore.Utilities.Check.NotNull[T](T value, String parameterName) in D:\git\EntityFrameworkCore\src\Shared\Check.cs:line 28
   at Microsoft.EntityFrameworkCore.Query.Expressions.ColumnExpression..ctor(String name, IProperty property, TableExpressionBase tableExpression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\Expressions\ColumnExpression.cs:line 36
   at Microsoft.EntityFrameworkCore.Query.Expressions.SelectExpression.BindProperty(IProperty property, IQuerySource querySource) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\Expressions\SelectExpression.cs:line 562
   at Microsoft.EntityFrameworkCore.Query.Expressions.SelectExpression.BindProperty(IProperty property, IQuerySource querySource) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\Expressions\SelectExpression.cs:line 562
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.<>c__28`1.<TryBindMemberOrMethodToSelectExpression>g__BindPropertyToSelectExpression|28_0(IProperty property, IQuerySource querySource, SelectExpression selectExpression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\ExpressionVisitors\SqlTranslatingExpressionVisitor.cs:line 753
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.<TryBindMemberOrMethodToSelectExpression>b__28_1[TExpression](IProperty property, IQuerySource querySource, SelectExpression selectExpression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\ExpressionVisitors\SqlTranslatingExpressionVisitor.cs:line 760
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.BindMemberOrMethod[TResult](Func`4 memberBinder, IQuerySource querySource, IProperty property, Boolean bindSubQueries) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\RelationalQueryModelVisitor.cs:line 2168
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.<>c__DisplayClass105_0`1.<BindMemberExpression>b__0(IProperty property, IQuerySource qs) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\RelationalQueryModelVisitor.cs:line 2045
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.<>c__DisplayClass91_0`1.<BindMemberExpression>b__0(IReadOnlyList`1 ps, IQuerySource qs) in D:\git\EntityFrameworkCore\src\EFCore\Query\EntityQueryModelVisitor.cs:line 1775
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.BindPropertyExpressionCore[TResult](Expression propertyExpression, IQuerySource querySource, Func`3 propertyBinder) in D:\git\EntityFrameworkCore\src\EFCore\Query\EntityQueryModelVisitor.cs:line 1823
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.BindMemberExpression[TResult](MemberExpression memberExpression, IQuerySource querySource, Func`3 memberBinder) in D:\git\EntityFrameworkCore\src\EFCore\Query\EntityQueryModelVisitor.cs:line 1769
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.BindMemberExpression[TResult](MemberExpression memberExpression, IQuerySource querySource, Func`4 memberBinder, Boolean bindSubQueries) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\RelationalQueryModelVisitor.cs:line 2043
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.BindMemberExpression[TResult](MemberExpression memberExpression, Func`4 memberBinder, Boolean bindSubQueries) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\RelationalQueryModelVisitor.cs:line 2031
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.<>c.<VisitMember>b__26_0(MemberExpression expression, RelationalQueryModelVisitor visitor, Func`4 binder) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\ExpressionVisitors\SqlTranslatingExpressionVisitor.cs:line 706
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.TryBindMemberOrMethodToSelectExpression[TExpression](TExpression sourceExpression, Func`4 binder) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\ExpressionVisitors\SqlTranslatingExpressionVisitor.cs:line 757
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.VisitMember(MemberExpression memberExpression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\ExpressionVisitors\SqlTranslatingExpressionVisitor.cs:line 704
   at System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.Visit(Expression expression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\ExpressionVisitors\SqlTranslatingExpressionVisitor.cs:line 127
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\ExpressionVisitors\SqlTranslatingExpressionVisitor.cs:line 604
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.Visit(Expression expression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\ExpressionVisitors\SqlTranslatingExpressionVisitor.cs:line 127
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.VisitExtension(Expression expression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\ExpressionVisitors\SqlTranslatingExpressionVisitor.cs:line 1099
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.Visit(Expression expression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\ExpressionVisitors\SqlTranslatingExpressionVisitor.cs:line 127
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.ProcessComparisonExpression(BinaryExpression binaryExpression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\ExpressionVisitors\SqlTranslatingExpressionVisitor.cs:line 529
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression expression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\ExpressionVisitors\SqlTranslatingExpressionVisitor.cs:line 162
   at Microsoft.EntityFrameworkCore.SqlServer.Query.ExpressionVisitors.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression) in D:\git\EntityFrameworkCore\src\EFCore.SqlServer\Query\ExpressionVisitors\Internal\SqlServerSqlTranslatingExpressionVisitor.cs:line 44
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.Visit(Expression expression) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\ExpressionVisitors\SqlTranslatingExpressionVisitor.cs:line 127
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitWhereClause(WhereClause whereClause, QueryModel queryModel, Int32 index) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\RelationalQueryModelVisitor.cs:line 993
   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) in D:\git\EntityFrameworkCore\src\EFCore\Query\EntityQueryModelVisitor.cs:line 910
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\RelationalQueryModelVisitor.cs:line 379
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel) in D:\git\EntityFrameworkCore\src\EFCore\Query\EntityQueryModelVisitor.cs:line 175
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](QueryModel queryModel) in D:\git\EntityFrameworkCore\src\EFCore\Storage\Database.cs:line 70

[smitpatel]

Also consider scenario in #12763

[julielerman]

just hitting this myself.

Based on my own tests, these scenarios are not supported when the defining query has navigations but they are fine (succeed and translate things like Where and FirstOrDefault into the SQL) when the DQ does not cintain any navigations.

I just want to confirm that I'm not missing some other case before I share this info.

Thanks and good finds @andriysavin! 👍

[darl0026]

I am still getting this exception (Value cannot be null. Parameter name: tableExpression) in preview 3 of 2.2 (2.2.0-preview3-35497).

        modelBuilder
            .Query<ProjectAuditQueryDto>()
            .ToQuery(() =>
                from cl in JobChangeLog
                join j in Job on cl.ArchiveNumber equals j.ArchiveNumber
                select new ProjectAuditQueryDto
                {
                    ArchiveNumber = j.ArchiveNumber,
                    ProjectName = j.Name,
                    FieldName = cl.FieldName,
                    DateOfChange = cl.TimeStamp,
                    ChangedByOnetUserId = cl.ChangedByOnetUserId,
                    NewFieldValue = cl.NewFieldValue,
                    OldFieldValue = cl.OldFieldValue
                });

Then this is the query that fails
.Query<ProjectAuditQueryDto>()
.Where(d => d.DateOfChange >= startDate &&
d.DateOfChange <= endDate &&
(includeSystemUser || d.ChangedByOnetUserId != Constants.SystemOwner))

[snickler]

There's two ways I was able to get past this:

1. Creating a Query that does nothing but return a blank IQueryable, but use FromSql when accessing it.

Example:

** in your DbContext

public DbQuery<SomeClass> SomeClasses {get;set;}

** in OnModelCreating

builder.Query<SomeClass>().ToQuery(() => Enumerable.Empty<SomeClass>().AsQueryable());

** in calling class, assuming _context is your DbContext

public IQueryable<SomeClass> SomeClasses => _context.SomeClasses.FromSql(/* insert raw sql statement with join */ );

2. Using the Lambda style syntax for querying, put an AsQueryable() before you Select the result

Example:

builder.Query<SomeClass>().ToQuery(() => 
           SomeDbSet
               .Where(x=>x.SomeValue == "something")
               .AsQueryable()
               .Select(result=> new SomeClass { SomeProperty = result.SomeDbSetValue }));

Doing either of these, I'm able to reference the query and issue conditional statements that translate to a SQL statement properly.

[smitpatel]

Assigning to @maumar since he is working on query filters (and defining queries)

[smitpatel]

Not needed as per #18903