SelectMany cannot be translated when manually defined.

[HT-Xaris]

Ask a question

Can someone help me, finding the mistake in the given code to generate a SelectMany-Expression?
Currently, I'm unable to resolve the parent parameter inside the "Where"-Expression.
It works agains an in memory list but fails on sql translation.

Include your code

Program.cs

using System.Linq.Expressions;

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;

using SampleExpressionToSql;

var context = new SampleContext();
context.Database.EnsureCreated();

// setup
var main = context.Set<MainModel>().Select(m => new { m = m });
var sub = context.Set<SubModel>().Select(s => new { s = s });

// original query
var compiledMany = main.SelectMany(m => sub.Where(s => s.s.MainId == m.m.Id), (m, s) => new Result(m.m, s.s));
Console.WriteLine(compiledMany.ToQueryString());

// manual query

// params
var subConstant = Expression.Constant(sub);
var mainParameter = Expression.Parameter(main.ElementType, "m");

// setup where
var anonymSubType = subConstant.Type.GetGenericArguments()[0];
var whereParameter = Expression.Parameter(anonymSubType, "s");

// left
var anonymSubTypePropertyS = anonymSubType.GetProperty("s")!;
var sPropertyExpression = Expression.Property(whereParameter, anonymSubTypePropertyS.GetMethod!);

var subMainIdProperty = typeof(SubModel).GetProperty("MainId")!;
var subMainIdPropertyExpression = Expression.Property(sPropertyExpression, subMainIdProperty.GetMethod!);

//right
var anonymMainTypePropertyM = main.ElementType.GetProperty("m")!;
var mPropertyExpression = Expression.Property(mainParameter, anonymMainTypePropertyM.GetMethod!);

var mainIdProperty = typeof(MainModel).GetProperty("Id")!;
var mainIdPropertyExpression = Expression.Property(mPropertyExpression, mainIdProperty.GetMethod!);

var compareExpression = Expression.Equal(subMainIdPropertyExpression, mainIdPropertyExpression);

var whereLambda = Expression.Lambda(compareExpression, [whereParameter]);
var whereCallExpression = Expression.Call(typeof(Queryable), nameof(Queryable.Where), [anonymSubType], subConstant, Expression.Quote(whereLambda));

// setup collection selector
var collectionSelectorLamda = Expression.Lambda(whereCallExpression, mainParameter);
var sourceLambdaInputType = main.Expression.Type.GetGenericArguments()[0];
var sourceLambdaResultType = collectionSelectorLamda.Body.Type.GetGenericArguments()[0];
var sourceLambdaEnumerableType = typeof(IEnumerable<>).MakeGenericType(sourceLambdaResultType);
var sourceLambdaDelegateType = typeof(Func<,>).MakeGenericType(sourceLambdaInputType, sourceLambdaEnumerableType);
var selectExpression = Expression.Lambda(sourceLambdaDelegateType, collectionSelectorLamda.Body, collectionSelectorLamda.Parameters);

//setup result selector
var resFirstParameter = Expression.Parameter(main.ElementType, "m");
var resSecondParameter = Expression.Parameter(sourceLambdaResultType, "s");

var mProperty = Expression.Property(resFirstParameter, resFirstParameter.Type.GetProperty("m")!.GetMethod!);
var sProperty = Expression.Property(resSecondParameter, resSecondParameter.Type.GetProperty("s")!.GetMethod!);

var resultCtor = typeof(Result).GetConstructor([typeof(MainModel), typeof(SubModel)])!;

var resultExpression = Expression.New(resultCtor,
    [ mProperty, sProperty ]
);

var resultLambdaExpression = Expression.Lambda(resultExpression, resFirstParameter, resSecondParameter);

var callExpression = Expression.Call(typeof(Queryable), nameof(Queryable.SelectMany),
                [main.ElementType, sourceLambdaResultType, resultLambdaExpression.Body.Type],
                main.Expression,
                Expression.Quote(selectExpression),
                Expression.Quote(resultLambdaExpression));

var manualResult = main.Provider.CreateQuery(callExpression);
Console.WriteLine(manualResult.ToQueryString());

context.Database.EnsureDeleted();

namespace SampleExpressionToSql
{
    public class SampleContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            _ = optionsBuilder.UseSqlServer($"Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog={Guid.NewGuid()};Integrated Security=True",
                options => _ = options.EnableRetryOnFailure());

            base.OnConfiguring(optionsBuilder);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            _ = modelBuilder.Entity<MainModel>()
                .HasData(
                [
                    new() { Id = 1, Name = $"{nameof(MainModel.Name)}1" },
                    new() { Id = 2, Name = $"{nameof(MainModel.Name)}2" }
                ]);

            _ = modelBuilder.Entity<SubModel>()
                .HasData(
                [
                    new() { Id = 1, Name = $"{nameof(MainModel.Name)}1", MainId = 1 },
                    new() { Id = 2, Name = $"{nameof(MainModel.Name)}2", MainId = 1 }
                ]);

            base.OnModelCreating(modelBuilder);
        }
    }

    internal class MainModel
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
    }

    internal class SubModel
    {
        public int Id { get; set; }
        public int MainId { get; set; }
        public string Name { get; set; } = string.Empty;
    }

    internal class Result(MainModel m, SubModel s)
    {
        public MainModel m { get; set; } = m;

        public SubModel s { get; set; } = s;
    }
}

namespace SampleExpressionToSql.Migrations
{
    [DbContext(typeof(SampleContext))]
    partial class SampleContextModelSnapshot : ModelSnapshot
    {
        protected override void BuildModel(ModelBuilder modelBuilder)
        {
            _ = modelBuilder.HasAnnotation("ProductVersion", "8.0.4").HasAnnotation("Relational:MaxIdentifierLength", 128);
            _ = SqlServerModelBuilderExtensions.UseIdentityColumns(modelBuilder);
            _ = modelBuilder.Entity("MainModel", b =>
            {
                _ = b.Property<int>("Id").ValueGeneratedOnAdd().HasColumnType("int");
                _ = SqlServerPropertyBuilderExtensions.UseIdentityColumn(b.Property<int>("Id"));
                _ = b.Property<string>("Name").IsRequired().HasColumnType("nvarchar(max)");
                _ = b.HasKey("Id");
                _ = b.ToTable("MainModel");
                _ = b.HasData(
                    new
                    {
                        Id = 1,
                        Name = "Name1"
                    },
                    new
                    {
                        Id = 2,
                        Name = "Name2"
                    });
            });

            _ = modelBuilder.Entity("SubModel", b =>
            {
                _ = b.Property<int>("Id").ValueGeneratedOnAdd().HasColumnType("int");
                _ = SqlServerPropertyBuilderExtensions.UseIdentityColumn(b.Property<int>("Id"));
                _ = b.Property<int>("MainId").HasColumnType("int");
                _ = b.Property<string>("Name").IsRequired().HasColumnType("nvarchar(max)");
                _ = b.HasKey("Id");
                _ = b.ToTable("SubModel");
                _ = b.HasData(
                    new
                    {
                        Id = 1,
                        MainId = 1,
                        Name = "Name1"
                    },
                    new
                    {
                        Id = 2,
                        MainId = 1,
                        Name = "Name2"
                    });
            });
        }
    }

    public partial class Initial : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            _ = migrationBuilder.CreateTable(
                name: "MainModel",
                columns: table => new
                {
                    Id = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Name = table.Column<string>(type: "nvarchar(max)", nullable: false)
                },
                constraints: table => table.PrimaryKey("PK_MainModel", x => x.Id));

            _ = migrationBuilder.CreateTable(
                name: "SubModel",
                columns: table => new
                {
                    Id = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    MainId = table.Column<int>(type: "int", nullable: false),
                    Name = table.Column<string>(type: "nvarchar(max)", nullable: false)
                },
                constraints: table => table.PrimaryKey("PK_SubModel", x => x.Id));

            _ = migrationBuilder.InsertData(
                table: "MainModel",
                columns: ["Id", "Name"],
                values: new object[,]
                {
                    { 1, "Name1" },
                    { 2, "Name2" }
                });

            _ = migrationBuilder.InsertData(
                table: "SubModel",
                columns: ["Id", "MainId", "Name"],
                values: new object[,]
                {
                    { 1, 1, "Name1" },
                    { 2, 1, "Name2" }
                });
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            _ = migrationBuilder.DropTable(name: "MainModel");
            _ = migrationBuilder.DropTable(name: "SubModel");
        }
    }

    [DbContext(typeof(SampleContext))]
    [Migration("20240505134838_Initial")]
    partial class Initial
    {
        protected override void BuildTargetModel(ModelBuilder modelBuilder)
        {
            _ = modelBuilder
                .HasAnnotation("ProductVersion", "8.0.4")
                .HasAnnotation("Relational:MaxIdentifierLength", 128);

            _ = SqlServerModelBuilderExtensions.UseIdentityColumns(modelBuilder);

            _ = modelBuilder.Entity("MainModel", b =>
            {
                _ = b.Property<int>("Id").ValueGeneratedOnAdd().HasColumnType("int");
                _ = SqlServerPropertyBuilderExtensions.UseIdentityColumn(b.Property<int>("Id"));
                _ = b.Property<string>("Name").IsRequired().HasColumnType("nvarchar(max)");
                _ = b.HasKey("Id");
                _ = b.ToTable("MainModel");
                _ = b.HasData(
                    new
                    {
                        Id = 1,
                        Name = "Name1"
                    },
                    new
                    {
                        Id = 2,
                        Name = "Name2"
                    });
            });

            _ = modelBuilder.Entity("SubModel", b =>
            {
                _ = b.Property<int>("Id").ValueGeneratedOnAdd().HasColumnType("int");
                _ = SqlServerPropertyBuilderExtensions.UseIdentityColumn(b.Property<int>("Id"));
                _ = b.Property<int>("MainId").HasColumnType("int");
                _ = b.Property<string>("Name").IsRequired().HasColumnType("nvarchar(max)");
                _ = b.HasKey("Id");
                _ = b.ToTable("SubModel");
                _ = b.HasData(
                    new
                    {
                        Id = 1,
                        MainId = 1,
                        Name = "Name1"
                    },
                    new
                    {
                        Id = 2,
                        MainId = 1,
                        Name = "Name2"
                    });
            });
        }
    }
}

Include stack traces

System.InvalidOperationException
  HResult=0x80131509
  Message=The LINQ expression '[{ s = SampleExpressionToSql.SubModel },{ s = SampleExpressionToSql.SubModel }]
    .Where(e => e.s.MainId == StructuralTypeShaperExpression: 
        SampleExpressionToSql.MainModel
        ValueBufferExpression: 
            ProjectionBindingExpression: EmptyProjectionMember
        IsNullable: False
    .Id)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
  Source=Microsoft.EntityFrameworkCore
  StackTrace:
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression) in Microsoft.EntityFrameworkCore.Query\QueryableMethodTranslatingExpressionVisitor.cs:line 55
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression) in Microsoft.EntityFrameworkCore.Query\RelationalQueryableMethodTranslatingExpressionVisitor.cs:line 827
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) in Microsoft.EntityFrameworkCore.Query\QueryCompilationContext.cs:line 91
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async) in Microsoft.EntityFrameworkCore.Storage\Database.cs:line 26
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) in Microsoft.EntityFrameworkCore.Query.Internal\QueryCompiler.cs:line 51
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0() in Microsoft.EntityFrameworkCore.Query.Internal\QueryCompiler.cs:line 46
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler) in Microsoft.EntityFrameworkCore.Query.Internal\CompiledQueryCache.cs:line 43
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) in Microsoft.EntityFrameworkCore.Query.Internal\QueryCompiler.cs:line 46
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) in Microsoft.EntityFrameworkCore.Query.Internal\EntityQueryProvider.cs:line 38
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToQueryString(IQueryable source) in Microsoft.EntityFrameworkCore\EntityFrameworkQueryableExtensions.cs:line 93
   at Program.<Main>$(String[] args) in C:\Entwicklung\Samples\SampleExpressionToSql\SampleExpressionToSql\Program.cs:line 79

Include provider and version information

csproj:

<PropertyGroup>
	<OutputType>Exe</OutputType>
	<TargetFramework>net8.0</TargetFramework>
	<ImplicitUsings>enable</ImplicitUsings>
	<Nullable>enable</Nullable>
</PropertyGroup>

<ItemGroup>
	<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.4" />
	<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="8.0.4">
		<PrivateAssets>all</PrivateAssets>
		<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
	</PackageReference>
</ItemGroup>

[roji]

@HT-Xaris we can try to take a look, but in general you have to construct a correct expression tree which matches what the C# compiler would construct; it's generally up to you to do this right.

One could way to understand what you need to align to, is to use the debugger to inspect the expression tree produced without actually executing the query. For example:

var query = context.Blogs.SelectMany(...);

Since the query isn't actually evaluated (there's no e.g. ToListAsync() at the end), query is an IQueryable, and you can look at the debugger to understand exactly what the correct structure looks like. Then, compare that to what you have constructed manually, and make sure things match - this way you can find the discrepancy and fix your code.

[HT-Xaris]

@roji

We can close the question. I tried what you described and also looked into the opcodes by ildasm.
The constant expression was my mistake because the opcode showed that it's needed but in this case there is an IQueryable value for the constant. The compiler is using a fieldexpression so that the inner expression is found.
For my problem the IQueryable was assigned directly so that the expression of the IQueryable was not used.

Thanks for your time. We can close the question.