search

npgsql / efcore.pg

Entity Framework Core provider for PostgreSQL
PostgreSQL License
1.52k stars 223 forks source link

`Parameter` in the SQL tree does not have a type mapping assigned after Upgrade to 8.0.0 #3014

Closed pinkfloydx33 closed 9 months ago

pinkfloydx33 commented 9 months ago

I have a query that is failing after upgrading to .NET8 and efcore.pg 8.0.0 with the error:

@__someparam_N in the SQL tree does not have a type mapping assigned after Upgrade to 8.0.0

I am not sure if this is efcore.pg specific, but the stack trace includes a bunch of Npgsql namespaces so I figured I will start here. The query involved includes a closed over variable of a type that is currently used in a jsonb mapping. The variable is first retrieved from a related entity and is then used in a subsequent query to correlate some values for display. In NET7/EF7 the query succeeds. After upgrade I receive the following error and stack trace:

Stack Trace ``` System.InvalidOperationException HResult=0x80131509 Message=Expression '@__dis_2' in the SQL tree does not have a type mapping assigned. Source=Microsoft.EntityFrameworkCore.Relational StackTrace: at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.RelationalInferredTypeMappingApplier.VisitExtension(Expression expression) in Microsoft.EntityFrameworkCore.Query\RelationalQueryableMethodTranslatingExpressionVisitor.cs:line 716 at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlQueryableMethodTranslatingExpressionVisitor.NpgsqlInferredTypeMappingApplier.VisitExtension(Expression expression) in /_/src/EFCore.PG/Query/Internal/NpgsqlQueryableMethodTranslatingExpressionVisitor.cs:line 1353 at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Expressions.Internal.PgUnnestExpression.VisitChildren(ExpressionVisitor visitor) in /_/src/EFCore.PG/Query/Expressions/Internal/PgUnnestExpression.cs:line 63 at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.RelationalInferredTypeMappingApplier.VisitExtension(Expression expression) in Microsoft.EntityFrameworkCore.Query\RelationalQueryableMethodTranslatingExpressionVisitor.cs:line 725 at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlQueryableMethodTranslatingExpressionVisitor.NpgsqlInferredTypeMappingApplier.VisitExtension(Expression expression) in /_/src/EFCore.PG/Query/Internal/NpgsqlQueryableMethodTranslatingExpressionVisitor.cs:line 1353 at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.g__VisitList|125_0[T](List`1 list, Boolean inPlace, Boolean& changed, <>c__DisplayClass125_0& ) in Microsoft.EntityFrameworkCore.Query.SqlExpressions\SelectExpression.cs:line 4482 at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.VisitChildren(ExpressionVisitor visitor) in Microsoft.EntityFrameworkCore.Query.SqlExpressions\SelectExpression.cs:line 4336 at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.RelationalInferredTypeMappingApplier.VisitExtension(Expression expression) in Microsoft.EntityFrameworkCore.Query\RelationalQueryableMethodTranslatingExpressionVisitor.cs:line 701 at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlQueryableMethodTranslatingExpressionVisitor.NpgsqlInferredTypeMappingApplier.VisitExtension(Expression expression) in /_/src/EFCore.PG/Query/Internal/NpgsqlQueryableMethodTranslatingExpressionVisitor.cs:line 1353 at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.g__VisitList|125_0[T](List`1 list, Boolean inPlace, Boolean& changed, <>c__DisplayClass125_0& ) in Microsoft.EntityFrameworkCore.Query.SqlExpressions\SelectExpression.cs:line 4483 at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.VisitChildren(ExpressionVisitor visitor) in Microsoft.EntityFrameworkCore.Query.SqlExpressions\SelectExpression.cs:line 4321 at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.RelationalInferredTypeMappingApplier.VisitExtension(Expression expression) in Microsoft.EntityFrameworkCore.Query\RelationalQueryableMethodTranslatingExpressionVisitor.cs:line 701 at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlQueryableMethodTranslatingExpressionVisitor.NpgsqlInferredTypeMappingApplier.VisitExtension(Expression expression) in /_/src/EFCore.PG/Query/Internal/NpgsqlQueryableMethodTranslatingExpressionVisitor.cs:line 1353 at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.RelationalInferredTypeMappingApplier.VisitExtension(Expression expression) in Microsoft.EntityFrameworkCore.Query\RelationalQueryableMethodTranslatingExpressionVisitor.cs:line 722 at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlQueryableMethodTranslatingExpressionVisitor.NpgsqlInferredTypeMappingApplier.VisitExtension(Expression expression) in /_/src/EFCore.PG/Query/Internal/NpgsqlQueryableMethodTranslatingExpressionVisitor.cs:line 1353 at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlQueryableMethodTranslatingExpressionVisitor.ApplyInferredTypeMappings(Expression expression, IReadOnlyDictionary`2 inferredTypeMappings) in /_/src/EFCore.PG/Query/Internal/NpgsqlQueryableMethodTranslatingExpressionVisitor.cs:line 257 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__DisplayClass12_0`1.b__0() in Microsoft.EntityFrameworkCore.Query.Internal\QueryCompiler.cs:line 66 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.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken) in Microsoft.EntityFrameworkCore.Query.Internal\QueryCompiler.cs:line 66 at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken) in Microsoft.EntityFrameworkCore.Query.Internal\EntityQueryProvider.cs:line 48 at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken) in Microsoft.EntityFrameworkCore.Query.Internal\EntityQueryable.cs:line 51 at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator() in System.Runtime.CompilerServices\ConfiguredCancelableAsyncEnumerable.cs:line 61 at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.d__67`1.MoveNext() in Microsoft.EntityFrameworkCore\EntityFrameworkQueryableExtensions.cs:line 471 at Program.<
$>d__0.MoveNext() in D:\Source\temp\Projects\Ef8Test\Program.cs:line 55 at Program.<
$>d__0.MoveNext() in D:\Source\temp\Projects\Ef8Test\Program.cs:line 61 at Program.
(String[] args) ```

I have reduced this down to as small of a repro as I could.

Entity Types Here are the entity types I am using: ```csharp public sealed class Report { public int Id { get; set; } public List ReportDisplay { get; } = new(); // stored as an array inside of jsonb public ICollection ReportData { get; set; } = new HashSet(); } public sealed class ReportDatum { public int Id { get; set; } public int ReportId { get; set; } public int SectionNumber { get; set; } public string? Value { get; set; } } public sealed class SectionInfo // stored as an array in a jsonb column { [JsonPropertyName("header")] public string? Header { get; set; } [JsonPropertyName("show")] public bool Show { get; set; } } ```
Context ```csharp public sealed class TestContext : DbContext { public DbSet Reports => Set(); public DbSet ReportData => Set(); private const string Conn = "Host=localhost;Port=5432;Database=ef8test;Username=postgres;Password=postgres;Include Error Detail=true"; private static readonly DbDataSource Datatsource = new Npgsql.NpgsqlDataSourceBuilder(Conn) #if NET8_0 .EnableDynamicJson() #endif .Build(); protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseNpgsql(Datatsource, e => e.SetPostgresVersion(11, 0)) .EnableDetailedErrors() .EnableSensitiveDataLogging() .LogTo(s => Debug.WriteLine(s)); } protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity(e => { e.ToTable("reports"); e.HasKey(p => p.Id); e.Property(p => p.Id) .HasColumnName("id") .UseIdentityByDefaultColumn(); e.Property(p => p.ReportDisplay) .HasColumnName("report_display") .IsRequired() .HasColumnType("jsonb") .HasDefaultValueSql("'[]'::jsonb"); e.HasMany(p => p.ReportData) .WithOne() .HasForeignKey(p => p.ReportId) .IsRequired(); }); modelBuilder.Entity(e => { e.ToTable("report_data"); e.HasKey(p => p.Id); e.Property(p => p.Id) .UseIdentityAlwaysColumn() .HasColumnName("id"); e.Property(p => p.ReportId) .HasColumnName("report_id"); e.Property(p => p.SectionNumber) .HasColumnName("section_number") .IsRequired(); e.Property(p => p.Value) .HasColumnName("value") .IsRequired(false); }); } } ```

Using the above, the following is an example program demonstrating the issue. Switch the target framework from net7.0 to net8.0 to see the exception.

Project File ```xml Exe net7.0 enable enable ```
Application code ```csharp using Microsoft.EntityFrameworkCore; await using (var ctx = new TestContext()) { await ctx.Database.EnsureDeletedAsync(); await ctx.Database.EnsureCreatedAsync(); } await using (var ctx = new TestContext()) { var rep = new Report { Id = 1, ReportData = new List { new() { SectionNumber = 0, Value = "a" }, new() { SectionNumber = 0, Value = "b" }, new() { SectionNumber = 1 }, new() { SectionNumber = 2 }, new() { SectionNumber = 3, Value = "c" }, new() { SectionNumber = 3 }, }, ReportDisplay = { new SectionInfo { Header = "One", Show = true }, new SectionInfo { Header = "Two", Show = false }, new SectionInfo { Header = "Three", Show = true }, } }; ctx.Add(rep); await ctx.SaveChangesAsync(); } await using (var ctx = new TestContext()) { var rep = (await ctx.FindAsync(1))!; var dis = rep.ReportDisplay; // value stored in a variable var query = ctx.ReportData.Where(e => e.ReportId == rep.Id) .GroupBy(e => e.SectionNumber) .OrderBy(e => e.Key) .Select(e => new { SectionNo = e.Key, Header = e.Key < dis.Count && dis[e.Key].Show ? dis[e.Key].Header : "", // value used to correlate Count = e.Count(), }); var data = await query.ToListAsync(); // error thrown here in .NET8 but not in .NET7 foreach (var res in data) { Console.WriteLine($"#{res.SectionNo} ('{res.Header}'): {res.Count}"); } } ```

In 7.x this prints:

0 ('One'): 2

1 (''): 1

2 ('Three'): 1

3 (''): 2

While in 8.x I receive the exception outlined above.

Versions used

Other Notes

If you instead use OwnsMany + ToJson, a different error is thrown with EFCore complaining about an untranslatable expression. We are unable to switch to this model because we need to set facets such as default value on the column (and thus continue to use the version of jsonb mapping available in .NET7) but I figured I'd give it a shot to see if it produced anything different.

Replace the configuration of the ReportDisplay property with the following:

e.OwnsMany(p => p.ReportDisplay, s => s.ToJson("report_display"));

Re-run the application in .NET8 mode. This time the exception is different:

The LINQ expression 's => s.Show' could not be translated

Stack Trace ``` System.InvalidOperationException HResult=0x80131509 Message=The LINQ expression 's => s.Show' 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.Relational StackTrace: at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitLambda[T](Expression`1 lambdaExpression) in Microsoft.EntityFrameworkCore.Query\RelationalSqlTranslatingExpressionVisitor.cs:line 669 at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in Microsoft.EntityFrameworkCore.Query\RelationalSqlTranslatingExpressionVisitor.cs:line 825 at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in /_/src/EFCore.PG/Query/Internal/NpgsqlSqlTranslatingExpressionVisitor.cs:line 271 at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in Microsoft.EntityFrameworkCore.Query\RelationalSqlTranslatingExpressionVisitor.cs:line 825 at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in /_/src/EFCore.PG/Query/Internal/NpgsqlSqlTranslatingExpressionVisitor.cs:line 271 at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression) in Microsoft.EntityFrameworkCore.Query\RelationalSqlTranslatingExpressionVisitor.cs:line 349 at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression) in /_/src/EFCore.PG/Query/Internal/NpgsqlSqlTranslatingExpressionVisitor.cs:line 218 at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitConditional(ConditionalExpression conditionalExpression) in Microsoft.EntityFrameworkCore.Query\RelationalSqlTranslatingExpressionVisitor.cs:line 566 at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression, Boolean applyDefaultTypeMapping) in Microsoft.EntityFrameworkCore.Query\RelationalSqlTranslatingExpressionVisitor.cs:line 206 at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateProjection(Expression expression, Boolean applyDefaultTypeMapping) in Microsoft.EntityFrameworkCore.Query\RelationalSqlTranslatingExpressionVisitor.cs:line 184 at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) in Microsoft.EntityFrameworkCore.Query.Internal\RelationalProjectionBindingExpressionVisitor.cs:line 220 at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitNew(NewExpression newExpression) in Microsoft.EntityFrameworkCore.Query.Internal\RelationalProjectionBindingExpressionVisitor.cs:line 460 at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) in Microsoft.EntityFrameworkCore.Query.Internal\RelationalProjectionBindingExpressionVisitor.cs:line 244 at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression) in Microsoft.EntityFrameworkCore.Query.Internal\RelationalProjectionBindingExpressionVisitor.cs:line 84 at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector) in Microsoft.EntityFrameworkCore.Query\RelationalQueryableMethodTranslatingExpressionVisitor.cs:line 1556 at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in Microsoft.EntityFrameworkCore.Query\QueryableMethodTranslatingExpressionVisitor.cs:line 355 at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in Microsoft.EntityFrameworkCore.Query\RelationalQueryableMethodTranslatingExpressionVisitor.cs:line 931 at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression) in Microsoft.EntityFrameworkCore.Query\QueryableMethodTranslatingExpressionVisitor.cs:line 48 at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression) in Microsoft.EntityFrameworkCore.Query\RelationalQueryableMethodTranslatingExpressionVisitor.cs:line 823 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__DisplayClass12_0`1.b__0() in Microsoft.EntityFrameworkCore.Query.Internal\QueryCompiler.cs:line 66 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.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken) in Microsoft.EntityFrameworkCore.Query.Internal\QueryCompiler.cs:line 66 at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken) in Microsoft.EntityFrameworkCore.Query.Internal\EntityQueryProvider.cs:line 48 at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken) in Microsoft.EntityFrameworkCore.Query.Internal\EntityQueryable.cs:line 51 at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator() in System.Runtime.CompilerServices\ConfiguredCancelableAsyncEnumerable.cs:line 61 at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.d__67`1.MoveNext() in Microsoft.EntityFrameworkCore\EntityFrameworkQueryableExtensions.cs:line 471 at Program.<
$>d__0.MoveNext() in D:\Source\temp\Projects\Ef8Test\Program.cs:line 55 at Program.<
$>d__0.MoveNext() in D:\Source\temp\Projects\Ef8Test\Program.cs:line 60 at Program.
(String[] args) ```

Changing the query so that projection is the following:

        .Select(e => new
        {
            SectionNo = e.Key,
            Header = e.Key < dis.Count ? dis[e.Key].Header : "",
            Count = e.Count(),
        });

Produces the same exception, albeit with a different problem expression (s => s.Header).

I have also tried to re-write the query such that it can be performed in a single roundtrip, specifically by adding a navigation property from the ReportDatum object back to the Report. I couldn't seem to get any form of the GroupBy to work so I don't think there's a workaround for me at the moment other than manually iterating the results and overwriting the output which is less than ideal.

roji commented 9 months ago

Thanks for the repro. Yes, this is a regression in 8.0, as a result of the new primitive collection support, which blocks the client evaluation that occurred in older versions (note that this was never actually translated to SQL). The problem isn't specific to PostgreSQL, I opened https://github.com/dotnet/efcore/issues/32634 to track this at the EF level.

pinkfloydx33 commented 9 months ago

It technically got translated as a constant boolean value in the SQL (based on the logic around the variable)... But I take your point :)

As always, thanks for looking!