Add EF Core 7 style JSON support (owns & `ToJson`)

[mkozlina]

Code

using Microsoft.EntityFrameworkCore;

public enum OrderStatus
{
    Pending,
    Shipped
}

public class StreetAddress
{
    public string Street { get; set; }
    public string City { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public OrderStatus Status { get; set; }
    public StreetAddress ShippingAddress { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<Order> Orders { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseMySql(
                "Server=localhost;Database=test;User=root",
                new MySqlServerVersion(new System.Version(8, 0)),
                o => o.UseMicrosoftJson());
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>()
            .OwnsOne(
                p => p.ShippingAddress,
                nb => nb.ToJson());
    }
}

public static class Program
{
    public static void Main(string[] args)
    {
        using var context = new MyContext();

        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        context.Add(new Order
        {
            Id = 101,
            Status = OrderStatus.Pending,
            ShippingAddress = new StreetAddress { City = "London", Street = "221 B Baker St" }
        });

        context.SaveChanges();
    }
}

Steps to reproduce

1. Build
2. Add migration
3. Run

The issue

Throws a DbUpdateException on context.SaveChanges(), with an inner InvalidCastException saying that it cannot cast from 'String' to 'JsonElement'.

Exception message: Invalid cast from 'System.String' to 'System.Text.Json.JsonElement'.
Stack trace:
   at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)
   at System.String.System.IConvertible.ToType(Type type, IFormatProvider provider)
   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at Microsoft.EntityFrameworkCore.Storage.ValueConversion.ValueConverter`2.Sanitize[T](Object value)
   at Microsoft.EntityFrameworkCore.Storage.ValueConversion.ValueConverter`2.<>c__DisplayClass4_0`2.<SanitizeConverter>b__1(Object v)
   at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMapping.CreateParameter(DbCommand command, String name, Object value, Nullable`1 nullable, ParameterDirection direction)
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypeMappedRelationalParameter.AddDbParameter(DbCommand command, Object value)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalParameterBase.AddDbParameter(DbCommand command, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.CreateDbCommand(RelationalCommandParameterObject parameterObject, Guid commandId, DbCommandMethod commandMethod)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)

Further technical details

Database version: MySQL v8.0.27 and MariaDB v10.6.11
Operating system: Windows 10 build 19044.2486
Pomelo.EntityFrameworkCore.MySql version: 7.0.0
Microsoft.AspNetCore.App: 6.0.13 and 7.0.2

Other info

I tried this on multiple machines and always end up with the same exception. It only happens when aggregating to JSON (nb.ToJson()).

[thboulay]

Same issue

[arunroy6]

Any solution or workaround? I am facing the same issue as well.

[wuheng742]

same issue, dose anyone has solution?

[lauxjpn]

Pomelo has extended JSON support for both, the Microsoft stack and the Newtonsoft stack. Because we already implemented JSON support a couple of years ago, it works a bit different than the simple JSON support now supported by EF Core.

For some (older) sample code, with lots of different ways to use JSON, see #14 (comment).

The basics steps are to reference either the Pomelo.EntityFrameworkCore.MySql.Json.Microsoft or Pomelo.EntityFrameworkCore.MySql.Json.Newtonsoft NuGet package, call .UseMicrosoftJson() or .UseNewtonsoftJson() and set the column type to json in the model (e.g. by using .HasColumnType("json")) for all columns that you want to use with JSON.

[Angelinsky7]

@lauxjpn does it mean that we cannot use OwnsOne or OwnsMany with ToJson call ?
Because it's actually not possible to something like this

builder.Property(p => p.Data).HasColumnType("json")
builder .OwnsOne(p => p.Data, dataBuilder => {
  dataBuilder.ToJson("data");
  dataBuilder.OwnsMany(p => p.Items); // Not necessary
});

The 2 properties are mutually exclusive :
The property or navigation 'Data' cannot be added to the entity type '' because a property or navigation with the same name already exists on entity type ''.

What would be the workaround to have OwnEntities with JSON representation in database with the current version of Pomelo ?

Thanks !

Edit:
For me the current exception is :
InvalidOperationException: This node should be handled by provider-specific sql generator.

Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitJsonScalar(JsonScalarExpression jsonScalarExpression)
Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlQuerySqlGenerator.VisitExtension(Expression extensionExpression)
Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitProjection(ProjectionExpression projectionExpression)
Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlQuerySqlGenerator.VisitExtension(Expression extensionExpression)
Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.<VisitSelect>b__21_0(ProjectionExpression e)
Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GenerateList<T>(IReadOnlyList<T> items, Action<T> generationAction, Action<IRelationalCommandBuilder> joinAction)
Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GenerateRootCommand(Expression queryExpression)
Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GetCommand(Expression queryExpression)
Microsoft.EntityFrameworkCore.Query.Internal.RelationalCommandCache.GetRelationalCommandTemplate(IReadOnlyDictionary<string, object> parameters)
Microsoft.EntityFrameworkCore.Internal.RelationCommandCacheExtensions.RentAndPopulateRelationalCommand(RelationalCommandCache relationalCommandCache, RelationalQueryContext queryContext)
Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable<T>+AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync<TState, TResult>(TState state, Func<DbContext, TState, CancellationToken, Task<TResult>> operation, Func<DbContext, TState, CancellationToken, Task<ExecutionResult<TResult>>> verifySucceeded, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable<T>+AsyncEnumerator.MoveNextAsync()
System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable<TResult>+ConfiguredValueTaskAwaiter.GetResult()
Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync<TSource>(IAsyncEnumerable<TSource> asyncEnumerable, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync<TSource>(IAsyncEnumerable<TSource> asyncEnumerable, CancellationToken cancellationToken)

With version :

<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="7.0.0" />
<PackageReference Include="Pomelo.EntityFrameworkCore.MySql.Json.Microsoft" Version="7.0.0" />

[vlad-rivera]

In case this helps someone I had this very exeption:

Exception message: Invalid cast from 'System.String' to 'System.Text.Json.JsonElement'.
Stack trace:
   at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)
   at System.String.System.IConvertible.ToType(Type type, IFormatProvider provider)
   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at Microsoft.EntityFrameworkCore.Storage.ValueConversion.ValueConverter`2.Sanitize[T](Object value)
   at Microsoft.EntityFrameworkCore.Storage.ValueConversion.ValueConverter`2.<>c__DisplayClass4_0`2.<SanitizeConverter>b__1(Object v)
   at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMapping.CreateParameter(DbCommand command, String name, Object value, Nullable`1 nullable, ParameterDirection direction)
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypeMappedRelationalParameter.AddDbParameter(DbCommand command, Object value)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalParameterBase.AddDbParameter(DbCommand command, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.CreateDbCommand(RelationalCommandParameterObject parameterObject, Guid commandId, DbCommandMethod commandMethod)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)

And what solved the issue for me was removing from the entity configuration

builder.OwnsOne(x => x.Properties, ownedNavigationBuilder =>
{
    ownedNavigationBuilder.ToJson();
});

and replacing that with:

builder.Property(x => x.Properties).HasColumnType("json");

[anasgauba]

@lauxjpn - I have the same question as @Angelinsky7. Will using the OwnsOne, OwnsMany with ToJson going to be supported in 8.0? So far, I have only seen the JSON column working is to do something like

builder.Property(p => p.Data)
   .HasColumnType("json");

as suggested. But I think this limits us to not be fine grained about how the owned JSON aggregate should be stored in the database.

For example, what if we want to ignore a subset of the JSON data if we wanted to be meticulous, can we achieve that? So, something like for example:

builder .OwnsOne(p => p.Data, dataBuilder => {
  dataBuilder.Ignore(p => p.SomeProp);
});

where we don't want to store a specific property part of Data type in the JSON column.

Does something like this already exists and you can point me to? Or will something like be supported in the 8.0 release or later?

[lauxjpn]

@anasgauba It is questionable if we will support the ToJson() feature of EF Core 7 in our 8.0.0 release.

However, since we have general support for both, the Microsoft JSON stack and the Newtonsoft JSON stack, all the general stack rules and features should be available, which include ignoring specific properties (e.g. How to ignore properties with System.Text.Json).

Here is a quick example, using the Microsoft JSON stack (by calling options.UseMicrosoftJson()), that uses the data model from the OP, and ignores the property StreetAddress.Comments:

Program.cs

using System;
using System.Diagnostics;
using System.Linq;
using System.Text.Json.Serialization;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate;

public enum OrderStatus
{
    Pending,
    Shipped
}

// JSON object class
public class StreetAddress
{
    public string Street { get; set; }
    public string City { get; set; }
    
    [JsonIgnore] // <-- ignore property "Comments"
    public string Comments { get; set; }
}

// EF Core entity
public class Order
{
    public int Id { get; set; }
    public OrderStatus Status { get; set; }
    public StreetAddress ShippingAddress { get; set; }
}

public class Context : DbContext
{
    public DbSet<Order> Orders { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue1752";
            var serverVersion = ServerVersion.AutoDetect(connectionString);

            optionsBuilder
                .UseMySql(
                    connectionString,
                    serverVersion,
                    options => options.UseMicrosoftJson()) // <-- enable json support
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>(
            entity =>
            {
                entity.Property(e => e.ShippingAddress)
                    .HasColumnType("json"); // <-- explicitly specify the column type as "json"
            });
    }
}

internal static class Program
{
    private static void Main()
    {
        using (var creationContext = new Context())
        {
            creationContext.Database.EnsureDeleted();
            creationContext.Database.EnsureCreated();
        }

        using (var saveContext = new Context())
        {
            saveContext.Add(
                new Order
                {
                    Id = 101,
                    Status = OrderStatus.Pending,
                    ShippingAddress = new StreetAddress
                    {
                        City = "London",
                        Street = "221 B Baker St",
                        Comments = "This property will be ignored.",
                    }
                });

            saveContext.SaveChanges();
        }
        
        using (var queryContext = new Context())
        {
            var orders = queryContext.Orders.ToList();

            Trace.Assert(orders.Count == 1);
            Trace.Assert(orders[0].Id == 101);
            Trace.Assert(orders[0].ShippingAddress.Comments is null);
        }
    }
}

Output (SQL)

warn: 23.11.2023 10:44:56.036 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure) 
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
info: 23.11.2023 10:44:56.479 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']                                 
      CREATE DATABASE `Issue1752`;                                                                                      
info: 23.11.2023 10:44:56.562 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER DATABASE CHARACTER SET utf8mb4;
info: 23.11.2023 10:44:56.586 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (23ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `Orders` (
          `Id` int NOT NULL AUTO_INCREMENT,
          `Status` int NOT NULL,
          `ShippingAddress` json NULL,
          CONSTRAINT `PK_Orders` PRIMARY KEY (`Id`)
      ) CHARACTER SET=utf8mb4;
info: 23.11.2023 10:44:56.799 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (25ms) [Parameters=[@p0='101', @p1='{"Street":"221 B Baker St","City":"London"}' (Size = 4000), @p2='0'], CommandType='Text', CommandTimeout='30']
      SET AUTOCOMMIT = 1;
      INSERT INTO `Orders` (`Id`, `ShippingAddress`, `Status`)
      VALUES (@p0, @p1, @p2);
info: 23.11.2023 10:44:56.991 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `o`.`Id`, `o`.`ShippingAddress`, `o`.`Status`
      FROM `Orders` AS `o`

If you want even more fine-grained control, you could override or replace the default value converter that we supply when you specify the json type:

public class MyCustomMySqlJsonMicrosoftPocoValueConverter<T> : MySqlJsonMicrosoftPocoValueConverter<T>
{
    public MyCustomMySqlJsonMicrosoftPocoValueConverter()
        : base(
            v => ConvertToProviderCore(v),
            v => ConvertFromProviderCore(v))
    {
    }

    private static string ConvertToProviderCore(T v)
        => JsonSerializer.Serialize(v); // do whatever you want here

    private static T ConvertFromProviderCore(string v)
        => JsonSerializer.Deserialize<T>(v); // do whatever you want here
}

// ...

entity.Property(e => e.ShippingAddress)
    .HasColumnType("json")
    .HasConversion<MyCustomMySqlJsonMicrosoftPocoValueConverter<StreetAddress>>()

Or declare your custom conversion inline:

entity.Property(e => e.ShippingAddress)
    .HasColumnType("json")
    .HasConversion(
        v => JsonSerializer.Serialize(v, (JsonSerializerOptions)null),
        v => JsonSerializer.Deserialize<StreetAddress>(v, (JsonSerializerOptions)null));

(@Angelinsky7 Feel free to ask follow-up questions, if something is unclear or confusing.)

[Angelinsky7]

@lauxjpn it feels sad, because for now it only means that pomelo don't "correctly" support ownsEntities and toJson...
thus i cannot use the same modelCreating for both mariadb and sqlServer ( what i was able to do without this)

[lauxjpn]

Yes, we currently don't support the ToJson syntax that EF Core 7 introduced.

However, we have been supporting JSON in a much more comprehensive way than ToJson years before that feature got finally introduced in EF Core. And we support not just the Microsoft stack, but also the Newtonsoft stack. You should have much more options and control with our implementation, than ToJson.

We will implement ToJson sometime in the near future for compatibility reasons though.

[Angelinsky7]

@lauxjpn i totally understand your point of view.... sadly i don't think people reason like this :
pomelo is a provider for ef core, even if you you have better support or more features, the ef core users "wish" to be able at least to have all the features that ef core sells (even if your way of doing them are better, and i agree for some youre doing a better job)

Like you said, you'll need to to this is the future for compatibility reasons, the sad thing is that it seem complicated for you to do it.

But thanks again for you work and your support (i have used the HasConversion replacement in my onw code with a compiler flag to switch to sqlServer when building the integration tests, not what i would like but it's working)