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:

  • SQL Client for Mac OS X that works with MS SQL Server
  • How to change date-time format?
  • How can I get the size of the Transaction Log in SQL 2005 programmatically?
  • Batch process to delete rows from a SQL Server table
  • How do I search and group by a date column into sets of duration ranges?
  • Remove words before and after 4th occurrence of a pattern
  • 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.

  • SQL and C# - Tabs from SQL database
  • Visual Studio 2015 SQL Server Data Tools missing “Add Table” option
  • c# EntityFramework CodeFirst migration creates a new column for existing one
  • Convert SQL geography to C#
  • C# Sync MS Access database to sql server
  • Issue when trying to read multiplte entity resultsets from a stored procedure
  • 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.