Pomelo.EntityFrameworkCore.MySql
Pomelo.EntityFrameworkCore.MySql copied to clipboard
Published
20 hours ago •
PomeloFoundation
PomeloFoundation
Feature request: provide a way to set JsonSerializer options
Currently when using UseMicrosoftJson there does not appear to be a way to specify the serialization options (JsonSerializerOptions) for JsonSerializer to use (eg the second parameter passed to JsonSerializer.Serialize)
You can't set these globally in System.Text.Json, so you are stuck with the defaults - so no way to change casing or null handling, converters, etc etc.
It would be best if you could pass these into UseMicrosoftJson
Any updates on this? i am having problem in the json TimeSpan deserialization, the converter i used globally doesn't apply here
"EndTime":{"Ticks":863990000000,"Days":0,"Hours":23,"Milliseconds":0,"Minutes":59,"Seconds":59,"TotalDays":0.999988425925926,"TotalHours":23.99972222222222,"TotalMilliseconds":86399000,"TotalMinutes":1439.9833333333333,"TotalSeconds":86399}
always return empty result for my case
@alandotic You should normally be able to set this up without having to specify custom JSON serializer options at all, just by specifying a JsonConverter for the TimeSpan property of your JSON (POCO) object explicitly:
Program.cs
using System;
using System.Diagnostics;
using System.Linq;
using System.Text.Json;
using System.Text.Json.Serialization;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace IssueConsoleTemplate
{
/// <summary>
/// EF Core entity.
/// </summary>
public class IceCream
{
public int IceCreamId { get; set; }
public string Name { get; set; }
public IceCreamDetails Details { get; set; }
}
/// <summary>
/// JSON object.
/// </summary>
public class IceCreamDetails
{
public double Kilojoule { get; set; }
[JsonConverter(typeof(TimeSpanConverter))] // <-- Setup converter for TimeSpan property
public TimeSpan MaxStorageDuration { get; set; }
}
/// <summary>
/// Custom JSON -- TimeSpan converter.
/// </summary>
public class TimeSpanConverter : JsonConverter<TimeSpan>
{
public override TimeSpan Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
=> TimeSpan.Parse(reader.GetString());
public override void Write(Utf8JsonWriter writer, TimeSpan value, JsonSerializerOptions options)
=> writer.WriteStringValue(value.ToString());
}
public class Context : DbContext
{
public DbSet<IceCream> IceCreams { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue1266";
var serverVersion = ServerVersion.AutoDetect(connectionString);
optionsBuilder
.UseMySql(connectionString, serverVersion, o => o.UseMicrosoftJson())
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<IceCream>(
entity =>
{
entity.Property(e => e.Details)
.HasColumnType("json");
entity.HasData(
new IceCream
{
IceCreamId = 1,
Name = "Vanilla",
Details = new IceCreamDetails
{
Kilojoule = 866.0,
MaxStorageDuration = new TimeSpan(180, 0, 0, 0)
}
},
new IceCream
{
IceCreamId = 2,
Name = "Chocolate",
Details = new IceCreamDetails
{
Kilojoule = 904.0,
MaxStorageDuration = new TimeSpan(270, 12, 30, 21, 987).Add(TimeSpan.FromTicks(12345))
}
},
new IceCream
{
IceCreamId = 3,
Name = "Matcha",
}
);
});
}
}
internal static class Program
{
private static void Main()
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var iceCreams = context.IceCreams
.OrderBy(i => i.IceCreamId)
.ToList();
Trace.Assert(iceCreams.Count == 3);
Trace.Assert(iceCreams[0].Details is not null);
Trace.Assert(iceCreams[0].Details.MaxStorageDuration == new TimeSpan(180, 0, 0, 0));
Trace.Assert(iceCreams[1].Details is not null);
Trace.Assert(iceCreams[1].Details.MaxStorageDuration == new TimeSpan(270, 12, 30, 21, 987).Add(TimeSpan.FromTicks(12345)));
Trace.Assert(iceCreams[2].Details is null);
}
}
}
Another way is to use JsonSerializerOptions after all and pass them to your own custom EF Core ValueConverter:
Program.cs
using System;
using System.Diagnostics;
using System.Linq;
using System.Text.Json;
using System.Text.Json.Serialization;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
using Microsoft.Extensions.Logging;
namespace IssueConsoleTemplate
{
/// <summary>
/// EF Core entity.
/// </summary>
public class IceCream
{
public int IceCreamId { get; set; }
public string Name { get; set; }
public IceCreamDetails Details { get; set; }
}
/// <summary>
/// JSON object.
/// </summary>
public class IceCreamDetails
{
public double Kilojoule { get; set; }
public TimeSpan MaxStorageDuration { get; set; }
}
/// <summary>
/// Custom System.Text.Json -- TimeSpan converter.
/// </summary>
public class TimeSpanConverter : JsonConverter<TimeSpan>
{
public override TimeSpan Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
=> TimeSpan.Parse(reader.GetString());
public override void Write(Utf8JsonWriter writer, TimeSpan value, JsonSerializerOptions options)
=> writer.WriteStringValue(value.ToString());
}
/// <summary>
/// Custom EF Core JSON ValueCoverter for System.Text.Json.
/// </summary>
public class MySqlJsonMicrosoftPocoValueConverterWithOptions<T> : ValueConverter<T, string>
{
public MySqlJsonMicrosoftPocoValueConverterWithOptions(JsonSerializerOptions options)
: base(
v => ConvertToProviderCore(v, options),
v => ConvertFromProviderCore(v, options))
{
}
private static string ConvertToProviderCore(T v, JsonSerializerOptions options)
=> JsonSerializer.Serialize(v, options);
private static T ConvertFromProviderCore(string v, JsonSerializerOptions options)
=> JsonSerializer.Deserialize<T>(v, options);
}
public class Context : DbContext
{
public DbSet<IceCream> IceCreams { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue1266_01";
var serverVersion = ServerVersion.AutoDetect(connectionString);
optionsBuilder
.UseMySql(connectionString, serverVersion, o => o.UseMicrosoftJson())
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// Setup your JSON serializer options.
var jsonSerializerOptions = new JsonSerializerOptions();
jsonSerializerOptions.Converters.Add(new TimeSpanConverter());
modelBuilder.Entity<IceCream>(
entity =>
{
entity.Property(e => e.Details)
.HasColumnType("json")
.HasConversion(
new MySqlJsonMicrosoftPocoValueConverterWithOptions<IceCreamDetails>(
jsonSerializerOptions)); // <-- Use your custom value converter
entity.HasData(
new IceCream
{
IceCreamId = 1,
Name = "Vanilla",
Details = new IceCreamDetails
{
Kilojoule = 866.0,
MaxStorageDuration = new TimeSpan(180, 0, 0, 0)
}
},
new IceCream
{
IceCreamId = 2,
Name = "Chocolate",
Details = new IceCreamDetails
{
Kilojoule = 904.0,
MaxStorageDuration = new TimeSpan(270, 12, 30, 21, 987).Add(TimeSpan.FromTicks(12345))
}
},
new IceCream
{
IceCreamId = 3,
Name = "Matcha",
}
);
});
}
}
internal static class Program
{
private static void Main()
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var iceCreams = context.IceCreams
.OrderBy(i => i.IceCreamId)
.ToList();
Trace.Assert(iceCreams.Count == 3);
Trace.Assert(iceCreams[0].Details is not null);
Trace.Assert(iceCreams[0].Details.MaxStorageDuration == new TimeSpan(180, 0, 0, 0));
Trace.Assert(iceCreams[1].Details is not null);
Trace.Assert(iceCreams[1].Details.MaxStorageDuration == new TimeSpan(270, 12, 30, 21, 987).Add(TimeSpan.FromTicks(12345)));
Trace.Assert(iceCreams[2].Details is null);
}
}
}
Finally, if you have many JSON properties you want to apply your custom ValueConverter to, you could just automate this with a loop at the end of your Fluent API definitions:
Program.cs
using System;
using System.ComponentModel.DataAnnotations.Schema;
using System.Diagnostics;
using System.Linq;
using System.Text.Json;
using System.Text.Json.Serialization;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
using Microsoft.Extensions.Logging;
namespace IssueConsoleTemplate
{
/// <summary>
/// EF Core entity.
/// </summary>
public class IceCream
{
public int IceCreamId { get; set; }
public string Name { get; set; }
[Column(TypeName = "json")]
public IceCreamDetails Details { get; set; }
}
/// <summary>
/// JSON object.
/// </summary>
public class IceCreamDetails
{
public double Kilojoule { get; set; }
public TimeSpan MaxStorageDuration { get; set; }
}
/// <summary>
/// Custom JSON -- TimeSpan converter.
/// </summary>
public class TimeSpanConverter : JsonConverter<TimeSpan>
{
public override TimeSpan Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
=> TimeSpan.Parse(reader.GetString());
public override void Write(Utf8JsonWriter writer, TimeSpan value, JsonSerializerOptions options)
=> writer.WriteStringValue(value.ToString());
}
//
// Custom EF Core JSON ValueCoverter for System.Text.Json:
//
public class MySqlJsonMicrosoftPocoValueConverterWithOptions<T> : ValueConverter<T, string>
{
public MySqlJsonMicrosoftPocoValueConverterWithOptions(JsonSerializerOptions options)
: base(
v => ConvertToProviderCore(v, options),
v => ConvertFromProviderCore(v, options))
{
}
private static string ConvertToProviderCore(T v, JsonSerializerOptions options)
=> JsonSerializer.Serialize(v, options);
private static T ConvertFromProviderCore(string v, JsonSerializerOptions options)
=> JsonSerializer.Deserialize<T>(v, options);
}
public class Context : DbContext
{
public DbSet<IceCream> IceCreams { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue1266_02";
var serverVersion = ServerVersion.AutoDetect(connectionString);
optionsBuilder
.UseMySql(connectionString, serverVersion, o => o.UseMicrosoftJson())
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<IceCream>(
entity =>
{
entity.HasData(
new IceCream
{
IceCreamId = 1,
Name = "Vanilla",
Details = new IceCreamDetails
{
Kilojoule = 866.0,
MaxStorageDuration = new TimeSpan(180, 0, 0, 0)
}
},
new IceCream
{
IceCreamId = 2,
Name = "Chocolate",
Details = new IceCreamDetails
{
Kilojoule = 904.0,
MaxStorageDuration = new TimeSpan(270, 12, 30, 21, 987).Add(TimeSpan.FromTicks(12345))
}
},
new IceCream
{
IceCreamId = 3,
Name = "Matcha",
}
);
});
//
// Set your custom converter on all JSON properties:
//
var jsonSerializerOptions = new JsonSerializerOptions();
jsonSerializerOptions.Converters.Add(new TimeSpanConverter());
var jsonValueConverter = new MySqlJsonMicrosoftPocoValueConverterWithOptions<IceCreamDetails>(
jsonSerializerOptions);
foreach (var jsonProperty in modelBuilder.Model.GetEntityTypes()
.SelectMany(
e => e.GetDeclaredProperties()
.Where(
p => string.Equals(p.GetColumnBaseName(), "json", StringComparison.OrdinalIgnoreCase))))
{
jsonProperty.SetValueConverter(jsonValueConverter);
}
}
}
internal static class Program
{
private static void Main()
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var iceCreams = context.IceCreams
.OrderBy(i => i.IceCreamId)
.ToList();
Trace.Assert(iceCreams.Count == 3);
Trace.Assert(iceCreams[0].Details is not null);
Trace.Assert(iceCreams[0].Details.MaxStorageDuration == new TimeSpan(180, 0, 0, 0));
Trace.Assert(iceCreams[1].Details is not null);
Trace.Assert(iceCreams[1].Details.MaxStorageDuration == new TimeSpan(270, 12, 30, 21, 987).Add(TimeSpan.FromTicks(12345)));
Trace.Assert(iceCreams[2].Details is null);
}
}
}
@lauxjpn
Will it be possible to configure options at the property level? For example:
builder
.Property(f => f.Metadata)
.HasColumnType("json")
.JsonSerializerOptions((options) => ...);
@lauxjpn
Will it be possible to configure options at the property level? For example:
builder .Property(f => f.Metadata) .HasColumnType("json") .JsonSerializerOptions((options) => ...);
Yeah, something similar to that is what we will implement.