No coercion operator is defined between types 'System.IO.MemoryStream' and 'System.Nullable`1[System.Text.Json.JsonElement]'

[taspeotis]

I've upgraded to Npgsql 8 and started getting this:

System.InvalidOperationException: No coercion operator is defined between types 'System.IO.MemoryStream' and 'System.Nullable`1[System.Text.Json.JsonElement]'.
   at System.Linq.Expressions.Expression.GetUserDefinedCoercionOrThrow(ExpressionType coercionType, Expression expression, Type convertToType)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.CreateGetValueExpression(ParameterExpression dbDataReader, Int32 index, Boolean nullable, RelationalTypeMapping typeMapping, Type type, IPropertyBase property)

In Program.cs I have added:

    static Program()
    {
#pragma warning disable CS0618 // Type or member is obsolete
        // https://www.npgsql.org/doc/release-notes/8.0.html#json-poco-and-other-dynamic-features-now-require-an-explicit-opt-in
        NpgsqlConnection.GlobalTypeMapper.EnableDynamicJson();
#pragma warning restore CS0618 // Type or member is obsolete
    }

But the error persists. I'll try and do a minimal reproducible example, but for now I believe it's this POCO causing grief:

public sealed class [ ...elided... ]
{
    [UsedImplicitly] public Guid Id { get; init; }

    public string Code { get; set; } = default!;

    public JsonElement? InnerError { get; set; }

    public string Message { get; set; } = default!;

    public string? Target { get; set; }
}

Npgsql 7.x does not have this error.

[taspeotis]

I tried all Enable... methods but they did not help.

Also tried changing JsonElement? to JsonElement but to generate the migration changing the nullability of the column in the database...

An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy.
System.NotSupportedException: The type mapping for 'JsonElement' has not implemented code literal generation.
   at Microsoft.EntityFrameworkCore.Storage.CoreTypeMapping.GenerateCodeLiteral(Object value)
   at Microsoft.EntityFrameworkCore.Design.Internal.CSharpHelper.UnknownLiteral(Object value)
   at Microsoft.EntityFrameworkCore.Migrations.Design.CSharpMigrationOperationGenerator.Generate(AlterColumnOperation operation, IndentedStringBuilder builder)
   at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid3[T0,T1,T2](CallSite site, T0 arg0, T1 arg1, T2 arg2)
   at Microsoft.EntityFrameworkCore.Migrations.Design.CSharpMigrationOperationGenerator.Generate(String builderName, IReadOnlyList`1 operations, IndentedStringBuilder builder)
   at Microsoft.EntityFrameworkCore.Migrations.Design.CSharpMigrationsGenerator.GenerateMigration(String migrationNamespace, String migrationName, IReadOnlyList`1 upOperations, IReadOnlyList`1 downOperations)
   at Microsoft.EntityFrameworkCore.Migrations.Design.MigrationsScaffolder.ScaffoldMigration(String migrationName, String rootNamespace, String subNamespace, String language)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.AddMigration(String name, String outputDir, String contextType, String namespace)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigrationImpl(String name, String outputDir, String contextType, String namespace)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigration.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
The type mapping for 'JsonElement' has not implemented code literal generation.

[roji]

@taspeotis I think I'm going to need a minimal repro for this - can you please post one? Also, did this work for you in 7.0, and regress in 8.0?

[UliPlabst]

I'm running in the same issue. I have a minimal repro here for you. I have my postgres up and running on localhost:5432 with user posgres.. My password is in environment variable PG_PASSWD. I created a database with name npgsql_jsonelement_issue_repro for testing.
Everything works for JsonElement but when using Nullable<JsonElement> the exception occurs.

[taspeotis]

@roji Yes it works in 7.x

@UliPlabst Thanks for that, I have my own version as well

using System.Text.Json;
using Microsoft.EntityFrameworkCore;
using Npgsql;

var dataSourceBuilder = new NpgsqlDataSourceBuilder
{
    ConnectionStringBuilder =
    {
        Host = "localhost",
        Database = "npgsql",
        Username = "postgres",
        Password = "postgres"
    }
};

// JSON all the things...
dataSourceBuilder.EnableDynamicJson();
dataSourceBuilder.EnableRecordsAsTuples();
dataSourceBuilder.EnableUnmappedTypes();

await using var dataSource = dataSourceBuilder.Build();
var dbContextOptionsBuilder = new DbContextOptionsBuilder();

dbContextOptionsBuilder.UseNpgsql(dataSource);

await using var context = new NpgsqlContext(dbContextOptionsBuilder.Options);

await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

await context.Database.ExecuteSqlRawAsync(
    """INSERT INTO "EntitiesWithNullableJsonElement" ("Id", "Json") VALUES (8, NULL);""");

await context.EntitiesWithNullableJsonElement.ToListAsync();

public sealed class NpgsqlContext(DbContextOptions options) : DbContext(options)
{
    public DbSet<EntityWithNullableJsonElement> EntitiesWithNullableJsonElement { get; set; }
}

public sealed class EntityWithNullableJsonElement
{
    public int Id { get; set; }
    
    public JsonElement? Json { get; set; }
}

[taspeotis]

Here's a repro for the second error where I tried to change JsonElement? to JsonElement to get things going.

using System.Text.Json;
using Microsoft.EntityFrameworkCore;
using Npgsql;

await using var context = new NpgsqlContext();

public sealed class NpgsqlContext : DbContext
{
    public DbSet<EntityWithNullableJsonElement> EntitiesWithNullableJsonElement { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        
        var dataSourceBuilder = new NpgsqlDataSourceBuilder
        {
            ConnectionStringBuilder =
            {
                Host = "localhost",
                Database = "npgsql",
                Username = "postgres",
                Password = "postgres"
            }
        };

        // JSON all the things...
        dataSourceBuilder.EnableDynamicJson();
        dataSourceBuilder.EnableRecordsAsTuples();
        dataSourceBuilder.EnableUnmappedTypes();
        
        var dataSource = dataSourceBuilder.Build();

        optionsBuilder.UseNpgsql(dataSource);
    }
}

public sealed class EntityWithNullableJsonElement
{
    public int Id { get; set; }
    
    public JsonElement? Json { get; set; }
}

1. dotnet ef migrations add WithNull

2. Change JsonElement? to JsonElement

  public sealed class EntityWithNullableJsonElement
  {
       public int Id { get; set; }
 
+      public JsonElement Json { get; set; }   
-      public JsonElement? Json { get; set; }
  }

3. dotnet ef migrations add WithoutNull

You'll get

An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy.
System.NotSupportedException: The type mapping for 'JsonElement' has not implemented code literal generation.
   at Microsoft.EntityFrameworkCore.Storage.CoreTypeMapping.GenerateCodeLiteral(Object value)
   at Microsoft.EntityFrameworkCore.Design.Internal.CSharpHelper.UnknownLiteral(Object value)
   at Microsoft.EntityFrameworkCore.Migrations.Design.CSharpMigrationOperationGenerator.Generate(AlterColumnOperation operation, IndentedStringBuilder builder)
   at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid3[T0,T1,T2](CallSite site, T0 arg0, T1 arg1, T2 arg2)

[taspeotis]

I should clarify that the migration issue: I'm unsure if it works in Npgsql 7, I just stumbled upon it while trying to change the model to work around whatever has Npgsql 8 unhappy with JsonElement?

But the initial issue, yes, Npgsql 7 handles it fine.

[UliPlabst]

In my case I'm sure that it worked in Npgsql 7.

[roji]

Thank you... It'll take me a few days to get around to it but I'll investigate.

[dnetguru]

Seeing the same problem after upgrading a project from npgsql 7 to 8 and trying to read a JsonElement? field. Changing the type of the field from JsonElement? to JsonElement makes the problem go away.

Stack trace for reference:

No coercion operator is defined between types 'System.IO.MemoryStream' and 'System.Nullable`1[System.Text.Json.JsonElement]'.

at System.Linq.Expressions.Expression.GetUserDefinedCoercionOrThrow(ExpressionType coercionType, Expression expression, Type convertToType)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.CreateGetValueExpression(ParameterExpression dbDataReader, Int32 index, Boolean nullable, RelationalTypeMapping typeMapping, Type type, IPropertyBase property)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.ExpressionVisitor.VisitMemberAssignment(MemberAssignment node)
   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
   at System.Linq.Expressions.ExpressionVisitor.Visit[T](ReadOnlyCollection`1 nodes, Func`2 elementVisitor)
   at System.Linq.Expressions.ExpressionVisitor.VisitMemberInit(MemberInitExpression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.ProcessShaper(Expression shaperExpression, RelationalCommandCache& relationalCommandCache, IReadOnlyList`1& readerColumns, LambdaExpression& relatedDataLoaders, Int32& collectionId)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.VisitShapedQuery(ShapedQueryExpression shapedQueryExpression)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.VisitExtension(Expression extensionExpression)
   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__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()

[llRandom]

Getting the same error. Also looks like it's working fine on 8.0.0-rc.2 and only breaks on 8.0.0

[taspeotis]

I am probably completely wrong but I feel like ClrType might need to be checked for Nullable<JsonElement> too?

https://github.com/npgsql/efcore.pg/blob/main/src/EFCore.PG/Metadata/Conventions/NpgsqlJsonElementHackConvention.cs

[UliPlabst]

I just tried to apply a workaround based on @taspeotis suggestion of the underlying issue. I can now confirm that applying a custom IPropertyAddedConvention fixed the issue in my reproduction repository. Here's the code

public class FixNpgsqlJsonElementHackConvention : IPropertyAddedConvention
{
    private NpgsqlJsonTypeMapping? _jsonTypeMapping;

    public void ProcessPropertyAdded(IConventionPropertyBuilder propertyBuilder, IConventionContext<IConventionPropertyBuilder> context)
    {
        var property = propertyBuilder.Metadata;

        if (property.ClrType == typeof(JsonElement?) && property.GetColumnType() is null)
        {
            property.SetTypeMapping(_jsonTypeMapping ??= new NpgsqlJsonTypeMapping("jsonb", typeof(JsonElement?)));
        }
    }
}

//and in DbContext
public class AppDbContext: DbContext
{
      //... omitted ....
     protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
     {
         configurationBuilder.Conventions.Add(_ => new FixNpgsqlJsonElementHackConvention());
     }
}

So as it appears with this workaround the issue is no longer blocking us from moving to .net8, hurray! I will merge the .net8 branch of my project tomorrow and see if the workaround works there as well but I am optimistic.

[UliPlabst]

Fyi I confirmed today, the workaround is working for my project

[kieranbenton]

Does anyone know if this might be causing this error (I'm using the workaround) and if theres any (simpleish) way around it?

I'm trying to query:

		return await Context.MethodsOfPayment
			.Include(x => x.payment_provider_configuration)
			// this should translate into sql
			// TODO: doesn't work at the moment as is?
			.Where(x => x.payment_provider_configuration.settings == null ? false : x.payment_provider_configuration.settings.Value.GetProperty(settingName).GetString() == settingValue)
			.ToListAsync(cancellationToken);

Where payment_provider_configuration is a JsonElement? column. Switching to a straight JsonElement seems to work.

[16:09:31.518 ERR] [Kaizen.AspNetCore.Http.Middleware.CustomExceptionHandlerMiddleware] Unhandled exception
System.InvalidOperationException: The LINQ expression 'DbSet<MethodOfPayment>()
    .Join(
        inner: DbSet<PaymentProviderConfiguration>(), 
        outerKeySelector: m => EF.Property<Guid?>(m, "payment_provider_configuration_id"), 
        innerKeySelector: p => EF.Property<Guid?>(p, "id"), 
        resultSelector: (o, i) => new TransparentIdentifier<MethodOfPayment, PaymentProviderConfiguration>(
            Outer = o, 
            Inner = i
        ))
    .Where(m => m.Inner.settings == null ? False : m.Inner.settings.Value.GetProperty(__settingName_0).GetString() == __settingValue_1)' could not be translated. Additional information: Translation of method 'System.Text.Json.JsonElement.GetProperty' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. 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.

[llRandom]

The workaround provided by UliPlabst works if you select entity as is but it still fails if you map it to DTO. I have the following entity:

public class A
{
  public int Id { get; set; }
  public SomeJson? SomeField { get; set; }
}

public class SomeJson
{
  public int[] SomeIds { get; set; }
}

With workaround it works when I query the whole entity with Set<T>().ToListAsync() but fails if I map it to a DTO:

Set<T>().Select(i => new
{
  Id = i.Id,
  SomeIds = (JsonElement?)(object?)i.SomeField!.SomeIds
})

But it worked for 7.x and 8.0.0-rc.2. I've also tried to remove (JsonElement?)(object?) cast but it gives another error: No coercion operator is defined between types 'System.String' and 'System.Int32[]'. (as it did in older versions)

[mattbdc]

Just ran into this in 8.0.5 (JsonElement? as a property - backed by a nullable jsonb colum - not supported), would be good to see PR #3008 merged

[devon-redgames]

Also seeing this in 8.0.5 when it previously worked in 7.x versions. Would be great to see #3008 merged.

[Misiu]

I got the same error, but when using SQL Server.
In worked fine in .NET7, when moved to .NET8 (Microsoft.EntityFrameworkCore.SqlServer 8.0.10) my solution stopped working.

A workaround was to add a converter:

.HasConversion(v => v.HasValue ? JsonSerializer.Serialize(v.Value, (JsonSerializerOptions)null!) : null,v => string.IsNullOrEmpty(v) ? null : JsonDocument.Parse(v, new JsonDocumentOptions()).RootElement);

not the efficient way, but it works.

In my model I have this property:

public JsonElement? Params { get; init; }

[roji]

Everyone, apologies for leaving this uninvestigated for so long... @UliPlabst's suggestion indeed turned out to be correct, I'll fix this for the next 8.0.x patch version.

@Misiu it makes sense for this issue to exist in the SQL Server provider, it would need a workaround hack similar to what Npgsql has, until dotnet/efcore#32192 is fixed.