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

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:

  • How do I find ' % ' with the LIKE operator in SQL Server?
  • SSMS Results to Grid - CRLF not preserved in copy/paste - any better techniques?
  • Hierarchical Category sorting with subCategory's ID
  • Shift wise datetime checking in sql server query
  • Why would moving our .NET /SQL Server website to a new host cause connection pool size to be exceeded?
  • VBScript Runtime Error '800a000d'
  • 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.

  • SQLCLR - Wrapping a COM call
  • How can I fix CA2100 Review SQL queries for security vulnerabilities issue
  • SQL Server : delay before output (a print statement) is produced
  • Calling a SQL Server stored procedure with linq service through c#
  • Connecting to database for Multi-Tenant application?
  • Using Enum for a data layer object's 'status' in C#
  • One Solution collect form web for “Entity framework core: The property expression 'xx' is not valid. The expression should represent a property access: 't => t.MyProperty'”

    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();
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.