Entity framework core: The property expression 'xx' is not valid. The expression should represent a property access: 't => t.MyProperty'

Question

Trying to query with where clause at the leaf level in entity framework core but getting this error:

Unhandled Exception: System.ArgumentException: The property expression 'c => {from Child t in c.Children where ([t].Name != "NoInc
lude") select [t]}' is not valid. The expression should represent a property access: 't => t.MyProperty'. For more information on
including related data, see http://go.microsoft.com/fwlink/?LinkID=746393.
   at Microsoft.EntityFrameworkCore.Internal.ExpressionExtensions.GetComplexPropertyAccess(LambdaExpression propertyAccessExpressi
on)
   at Microsoft.EntityFrameworkCore.Query.ResultOperators.Internal.ThenIncludeExpressionNode.ApplyNodeSpecificSemantics(QueryModel
 queryModel, ClauseGenerationContext clauseGenerationContext)
   at Remotion.Linq.Parsing.Structure.IntermediateModel.MethodCallExpressionNodeBase.Apply(QueryModel queryModel, ClauseGeneration
Context clauseGenerationContext)
   at Remotion.Linq.Parsing.Structure.QueryParser.GetParsedQuery(Expression expressionTreeRoot)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](Expression query, INodeTypeProvider nod
eTypeProvider, IDatabase database, ILogger logger, Type contextType)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass19_0`1.<CompileQuery>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Remotion.Linq.QueryableBase`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at dotnetsql.Demo.Main(String[] args) in C:\Users\asad\dev\dotnetsql\Program.cs:line 15

Here is the complete code which can be used in a console application to replicate this:

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace dotnetsql
{
    class Demo
    {
        static void Main(string[] args)
        {
            using (var context = new FamilyContext())
            {
                var seed = new SeedData(context);
                var result = context.GrandParent
                .Include(p => p.Parents)
                .ThenInclude(c => c.Children.Where(t => t.Name != "NoInclude"))
                .ToList();
                Console.WriteLine(result.Count);

            }
        }

    }

    public class FamilyContext : DbContext
    {
        public FamilyContext()
        {
            Database.EnsureCreated();
        }


        public DbSet<GrandParent> GrandParent { get; set; }
        public DbSet<Parent> Parent { get; set; }
        public DbSet<Child> Child { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseInMemoryDatabase();
        }
    }
    public class GrandParent
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Parent> Parents { get; set; }
    }
    public class Parent
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int GrandParentId { get; set; }
        public GrandParent GrandParent { get; set; }
        public List<Child> Children { get; set; }
    }
    public class Child
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int ParentId { get; set; }
        public Parent Parent { get; set; }
    }

    // Seed Data
    public class SeedData
    {
        public SeedData(FamilyContext context)
        {
            if (!context.GrandParent.Any())
            {
                context.GrandParent.AddRange(
                    new List<GrandParent>
                    {
                        new GrandParent{Name = "Grandparent 1", },
                        new GrandParent{Name = "Grandparent 2", },
                    }

                );
                context.SaveChanges();
                context.Parent.AddRange(
                    new List<Parent> {
                    new Parent{Name = "Parent 1", GrandParentId = 1},
                    new Parent{Name = "Parent 2", GrandParentId = 1},
                    }
                );
                context.SaveChanges();
                context.Child.AddRange(
                    new List<Child> {
                    new Child{Name = "Child 1", ParentId = 1},
                    new Child{Name = "Child 2", ParentId = 1},
                    new Child{Name = "NoInclude", ParentId = 1}
                    }
                );
                context.SaveChanges();
            }
        }


    }



}

Read this article but lazy loading example is simple and expect to load a single entity before filtering its children records which isn't helping.

Here is the complete console application to quickly replicate this.

Thanks.

Answer 1

Assuming you want to perform a filtered include - a feature requested many times and not supported in any EF version including Core. AFAIK there is a plan to add it to EF Core, but until then you have to resolve it manually.

The technique is basically the same as explained in the Loading Related Data - Explicit loading section, filter which related entities are loaded into memory example, but applied to any query.

You start by extracting (but not executing) the aggregate root query (GrandParents in your example) applying all necessary filtering:

var query = context.GrandParent.AsQueryable();

Then execute and materialize the main query, applying non filtered includes:

var result = query
    .Include(p => p.Parents)
    .ToList();

Finally explicitly load the intended filtered includes:

query.SelectMany(p => p.Parents)
    .SelectMany(c => c.Children)
    .Where(t => t.Name != "NoInclude")
    .Load();