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

[eldroid-z]

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 https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/14#issuecomment-725257107.

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" />

[vlaxter]

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

```c# 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 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( 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)

[lauxjpn]

I'll put it on the list for the next release (8.0.0-silver.1), but no promises.

[LiangZugeng]

My app uses Pomelo in the production, and it uses Sqlite in-memory provider for integration tests, the model configuration is as follows.

builder.Entity<DeviceUpdate>(b =>
    {
      b.ToTable(BMSConsts.DbTablePrefix + "DeviceUpdates", BMSConsts.DbSchema);

      b.Property(x => x.Version).IsRequired().HasMaxLength(DeviceUpdateConsts.MaxVersionLength);
      b.Property(x => x.SizeInBytes).IsRequired();

      b.Property(x => x.Files).HasColumnType("json");
    });

I got an error running integration tests, looks like Sqlite in-memory database doesn't support .HasColumnType("json").

System.InvalidOperationException : The 'List<DeviceUpdateFile>' property 'DeviceUpdate.Files' could not be mapped to the database type 'json' because the database provider does not support mapping 'List<DeviceUpdateFile>' properties to 'json' columns. Consider mapping to a different database type or converting the property value to a type supported by the database using a value converter. See https://aka.ms/efcore-docs-value-converters for more information. Alternately, exclude the property from the model using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

Could you please suggest a way for me to use JSON for Files property that works with both Pomelo provider and Sqlite in-memory provider?

Could you also share the progress on Pomelo provider v8.0 supporting ToJson feature in EF8? If it is supported in Pomelo v8.0, I will upgrade the app to .NET 8 and EF8 and config the property using ToJson (Sqlite already supports ToJson in EF8).

[roji]

I got an error running integration tests, looks like Sqlite in-memory database doesn't support .HasColumnType("json").

@LiangZugeng that's very much expected, SQLite is a different database and cannot behave like MySQL. It's highly recommended to test against MySQL instead, using something like testcontainers, which allow you to very easily spin up MySQL in a container and test against it. For more information on testing against fakes vs. against your actual database system, see these EF docs.

[LiangZugeng]

@roji thanks for the advice of using testcontainers or similar for integration tests, I have been using Sqlite In-memory provider for integration tests all the time, but I'll try it out (it may be the better way).

The reason I raised this question is that one of my .NET 6 projects is using Innofactor.EfCoreJsonValueConverter to convert properties to JSON (I have no use of query of JSON properties so that's relatively simpler), and this approach works with both Pomelo MySQL provider and Sqlite In-memory provider without changing any DBContext configuration code. Innofactor author decided to stop maintain that library as .NET 7's ToJson feature and she/he suggested using the new feature. While I'm starting a .NET 8 application, I just want to have a unified way for EF Core DbContext configurations.

Anyway, while waiting for Pomelo provider to support ToJson feature in EF8, I changed my design to map the two entities to two tables in a traditional way, after Pomelo officially supports the new feature I will add a migration to change the DB design then.

[roji]

@LiangZugeng if what you're looking for is to just store JSON text data (no querying, partial updating or the more advanced logic), you can absolutely continue doing that via a value converter. I have no idea what the Innofactor package does, but just defining a value converter that serializes/deserializes to JSON should be trivial - you probably don't need a package to do that.

[LiangZugeng]

@roji Yep, I added the code as a value converter and everything works fine. Thank you.

[pantonis]

I'm confused. ToJson was added or not in the version of EF Core 8?

[pantonis]

I have a List in my entity and to make it work with EF Core I need to create an extra table. Whereas with ToJson this could be stored as json and retrieved/queried easily.

[meriturva]

Just a comment about this issue. I've been using JSON support with Pomelo for a while but now that a few enterprise applications I'm developing are getting bigger we are introducing integration tests with WebApplicationFactory approach.

To speed up this kind of test we are using SqlLite (in memory connection) instead a mariadb docker one (we have already persistence tests).

My problem now is that SqlLite supports toJson since ef core 8.0 and Pomelio supports only HasColumnType('json').

For now, we have found a workaround using property value converters, on other applications we have different EntityTypeConfiguration to configure different entity relations just for tests (not optimal for sure).

I write this comment only to point out that in these contexts support ef core standards will be greatly appreciated.

[lauxjpn]

To speed up this kind of test we are using SqlLite (in memory connection) instead a mariadb docker one (we have already persistence tests).

@meriturva Without wanting to dive deeper into the subject here, I highly suggest to use an instance similar to your actual target database in your integrations tests. Sqlite and its EF Core providers are different in their feature set and query capabilities to Pomelo and MariaDB. My practical suggestion would be to create docker containers as part of the integration test initialization instead.

[roji]

Testing is a very recurring question, and I very much agree with @lauxjpn that using SQLite as a database fake is a bad idea. I'll just add that testcontainers make it very easy to spin up a database in a container as part of the tests.

[meriturva]

Thanks, @roji and @lauxjpn I know your advice and, in fact, we have different tests with testcontainer.net and it works like a charm.

What I would like to underline is that enterprise projects have different facets and technical evolutions. There are many integrations with many layers to talk about and test. Sometimes you make choices, even questionable ones, but all this is the result of technical discussions on where you want to place your attention in your tests. Having said this, I reiterate that having standard support means being able to make choices that are otherwise locked to the chosen ef core driver.

My two cents.

[xkzhud]

how about nest class? like
public class DataSetProcessRules : FullAuditedEntity, IMultiTenant { public Guid? TenantId { get; set; } [Required] public Guid DataSetsId { get; set; }

[Required]
public ProcessingRules Rules { get; set; } = new ProcessingRules();

}

public class PerProcessingRules {

public bool Remove_Urls_Emails { get; set; } = false;

public bool Remove_Extra_Spaces = true;

}

public class SegmentationRulse { public int MaxTokensPerParagraph { get; set; } = 1000;

public int MaxTokensPerLine { get; set; } = 300;

public int OverlappingTokens { get; set; } = 100;

public string Delimiter { get; set; } = " \n";

}

builder.Entity(b => b.Property(p => p.Rules).HasColumnType("json"));

it can not works