Efcore: EF Core 3.1.9 for SQLite: System.InvalidOperationException: 'Lambda expression used inside Include is not valid.'

[bairog]

I have the following entities hierarchy:

• UserRole
  
  
  
  • User
  
  
  • Picture
  
  
  • Message
  
  

When I'm trying to get all UserRoles with Users (with Pictures and Meassages) via

context.UserRoles.Include(ur => ur.Users).ThenInclude(u => u.Messages)
.Include(ur => ur.Users).ThenInclude(u => u.Pictures).ToList();

it is working as expected.

But using

context.UserRoles.Include(ur => ur.Users.Select(us => us.Messages))
.Include(ur => ur.Users.Select(us => us.Pictures)).ToList();

results in exception:

System.InvalidOperationException: 'Lambda expression used inside Include is not valid.'

My Entites

public class UserRole
    {
        public Guid Id { get; set; }
        public String Name { get; set; }
        public virtual IList<User> Users { get; set; }
    }
    public class User
    {
        public Guid Id { get; set; }
        public String Name { get; set; }
        public Guid UserRoleId { get; set; }
        public virtual UserRole UserRole { get; set; }
        public virtual IList<Message> Messages { get; set; }
        public virtual IList<Picture> Pictures { get; set; }
    }
    public class Message
    {
        public Guid Id { get; set; }
        public String Text { get; set; }
        public Guid UserId { get; set; }
        public virtual User User { get; set; }
    }
    public class Picture
    {
        public Guid Id { get; set; }
        public String Name { get; set; }
        public Guid UserId { get; set; }
        public virtual User User { get; set; }
    }

My DbContext

class MyDbContext : DbContext
    {
        public MyDbContext()
        {
        }

        public MyDbContext(DbContextOptions<MyDbContext> options)
            : base(options)
        {
        }

        public DbSet<Picture> Pictures { get; set; }
        public DbSet<Message> Messages { get; set; }
        public DbSet<User> Users { get; set; }
        public DbSet<UserRole> UserRoles { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlite(@"data source=Users.db");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Message>().Property(t => t.Text).HasMaxLength(1024);
            modelBuilder.Entity<Message>().Property(t => t.Text).IsRequired();

            modelBuilder.Entity<User>().Property(t => t.Name).HasMaxLength(256);
            modelBuilder.Entity<User>().Property(t => t.Name).IsRequired();
        }
    }

Sample repo with my test

Stack trace

Unhandled exception. System.InvalidOperationException: Lambda expression used inside Include is not valid..
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ProcessInclude(NavigationExpansionExpression source, Expression expression, Boolean thenInclude)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.IncludableQueryable`2.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at DataAccessLayer.StorageSystem.GetUserRolesEagerLoading() in D:\DevartTest2\DataAccessLayer\StorageSystem.cs:line 18
   at Test.Program.Main(String[] args) in D:\DevartTest2\Test\Program.cs:line 9

Further technical details

EF Core version: 3.1.9
Database provider: Microsoft.EntityFrameworkCore.SQLite
Target framework: .NET Core 3.1
Operating system: Windows 10 x64 1909
IDE: Visual Studio 2019 16.7.6

[smitpatel]

Using Select inside Include was never supported in EF Core. Please use ThenInclude API.
More documentation on eager loading https://docs.microsoft.com/en-us/ef/core/querying/related-data/eager