Is there a way to scaffold mysql json into custom type?-CodePudding

Table looks like:

CREATE TABLE IF NOT EXISTS `spck`.`user_chapter` (
  `user_id` INT NOT NULL,
  `chapter_id` INT NOT NULL,
  `chests` JSON NOT NULL,
  PRIMARY KEY (`user_id`, `chapter_id`))
ENGINE = InnoDB;

chests value will be like "[1, 2, 3]". So I want to map chests into int[] or IList<int>.

But with dotnet-ef scaffold

dotnet ef dbcontext scaffold "Server=127.0.0.1;Port=3306;Database=spck;User=root;Password=;TreatTinyAsBoolean=true;" "Pomelo.EntityFrameworkCore.MySql" -o Generated/ -f

What I got is

public partial class UserChapter
{
    public int UserId { get; set; }
    public int ChapterId { get; set; }
    public string Chests { get; set; } = null!;
}

modelBuilder.Entity<UserChapter>(entity =>
{
    entity.HasKey(e => new { e.UserId, e.ChapterId })
        .HasName("PRIMARY")
        .HasAnnotation("MySql:IndexPrefixLength", new[] { 0, 0 });

    entity.ToTable("user_chapter");

    entity.Property(e => e.UserId).HasColumnName("user_id");

    entity.Property(e => e.ChapterId).HasColumnName("chapter_id");

    entity.Property(e => e.Chests)
        .HasColumnType("json")
        .HasColumnName("chests");
});

I can change the code manual, change type into int[] and add HasConversion into Entity Options.

public partial class UserChapter
{
    public int UserId { get; set; }
    public int ChapterId { get; set; }
    public int[] Chests { get; set; } = null!;
}

modelBuilder.Entity<UserChapter>(entity =>
{
    entity.HasKey(e => new { e.UserId, e.ChapterId })
        .HasName("PRIMARY")
        .HasAnnotation("MySql:IndexPrefixLength", new[] { 0, 0 });

    entity.ToTable("user_chapter");

    entity.Property(e => e.UserId).HasColumnName("user_id");

    entity.Property(e => e.ChapterId).HasColumnName("chapter_id");

    entity.Property(e => e.Chests)
        .HasColumnType("json")
        .HasColumnName("chests")
        .HasConversion<int[]>(str => JsonConvert.DeserializeObject<int[]>(str) ?? Array.Empty<int>(), list => JsonConvert.SerializeObject(list));
});

But I don't think it is a good way to do this manual. Is there some config can let dotnet-ef scaffold do this?

CodePudding user response：

Here are two options, to map a json store type to a specific CLR type.

Option 1: Map a store type to a single CLR type

If you want to map a store type to a single CLR type of a property when scaffolding, you can do the following:

First, start by adding a reference for one of the two JSON libraries supported by Pomelo to your project file. Since you seem to be using the Newtonsoft implementation in your sample code, I'll assume that JSON stack for the rest of the answer.

<ItemGroup>
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="6.0.2" />
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql.Json.Newtonsoft" Version="6.0.2" />
</ItemGroup>

Then add the following code to your project, to hook into the type mapping mechanism of Pomelo and EF Core:

public class MyDesignTimeServices : IDesignTimeServices
{
    public void ConfigureDesignTimeServices(IServiceCollection services)
    {
        // Setup our own implementation based on the default one.
        services.AddSingleton<IRelationalTypeMappingSourcePlugin, CustomMySqlJsonNewtonsoftTypeMappingSourcePlugin>();

        // Add all default implementations.
        services.AddEntityFrameworkMySqlJsonNewtonsoft();
    }
}

public class CustomMySqlJsonNewtonsoftTypeMappingSourcePlugin : MySqlJsonNewtonsoftTypeMappingSourcePlugin
{
    public CustomMySqlJsonNewtonsoftTypeMappingSourcePlugin(IMySqlOptions options)
        : base(options)
    {
    }

    public override RelationalTypeMapping FindMapping(in RelationalTypeMappingInfo mappingInfo)
    {
        if (string.Equals(mappingInfo.StoreTypeNameBase, "json", StringComparison.OrdinalIgnoreCase) &&
            mappingInfo.ClrType is null)
        {
            var customMappingInfo = new RelationalTypeMappingInfo(
                typeof(int[]), // <-- your target CLR type
                mappingInfo.StoreTypeName,
                mappingInfo.StoreTypeNameBase,
                mappingInfo.IsKeyOrIndex,
                mappingInfo.IsUnicode,
                mappingInfo.Size,
                mappingInfo.IsRowVersion,
                mappingInfo.IsFixedLength,
                mappingInfo.Precision,
                mappingInfo.Scale);

            return base.FindMapping(customMappingInfo);
        }

        return base.FindMapping(mappingInfo);
    }
}

Now scaffold your database:

dotnet ef dbcontext scaffold 'server=127.0.0.1;port=3306;user=root;password=;database=So73086923' 'Pomelo.EntityFrameworkCore.MySql' --context 'Context' --verbose --force

A class like the following has now been generated, that uses the correct CLR type for the Chests property:

public partial class UserChapter
{
    public int UserId { get; set; }
    public int ChapterId { get; set; }
    public int[] Chests { get; set; }
}

To use the generated classes in your app, add a UseNewtonsoftJson() call to your context configuration code:

public partial class Context : DbContext
{
    // ...

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
            optionsBuilder.UseMySql(
                "server=127.0.0.1;port=3306;user=root;database=So73086923",
                Microsoft.EntityFrameworkCore.ServerVersion.Parse("8.0.25-mysql"),
                builder => builder.UseNewtonsoftJson()) // <-- initialize JSON support
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableDetailedErrors()
                .EnableSensitiveDataLogging();
        }
    }

    // ...
}

You can now use your context:

private static void Main()
{
    // We first clear the `user_chapter` table and then populate it with some test rows.
    using (var context = new Context())
    {
        context.UserChapters.RemoveRange(context.UserChapters.ToList());
        
        context.UserChapters.AddRange(
            new UserChapter { ChapterId = 1, UserId = 1, Chests = new[] { 1, 2, 3 } },
            new UserChapter { ChapterId = 1, UserId = 2, Chests = new[] { 4, 5, 6 } },
            new UserChapter { ChapterId = 2, UserId = 2, Chests = new[] { 7, 8, 9 } });

        context.SaveChanges();
    }

    using (var context = new Context())
    {
        var chapters = context.UserChapters
            .OrderBy(c => c.ChapterId)
            .ThenBy(c => c.UserId)
            .ToList();
        
        Trace.Assert(chapters.Count == 3);
        Trace.Assert(chapters[1].Chests[1] == 5);
    }
}

As long as your properties are declared to use the json database type, Pomelo will serialize/deserialize them for you as JSON.

Option 2: Map a store type to a dynamically chosen CLR type

If you want to dynamically map a store type to a CLR type of a property when scaffolding, you can do the following:

Also ensure, that your <IncludeAssets> subtag of your Microsoft.EntityFrameworkCore.Design package reference, contains the compile value:

<ItemGroup>
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="6.0.2" />
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql.Json.Newtonsoft" Version="6.0.2" />
    
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.7">
        <PrivateAssets>all</PrivateAssets>
        <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive; compile</IncludeAssets> <!-- contains "compile" -->
    </PackageReference>
</ItemGroup>

Then add the following code to your project, to hook into the scaffolding model factory mechanism of EF Core, that translates the read database model (with tables and columns) into a relational model (with entities and properties):

public class MyDesignTimeServices : IDesignTimeServices
{
    public void ConfigureDesignTimeServices(IServiceCollection services)
    {
        // Setup our own implementation based on the default one.
        services.AddSingleton<IScaffoldingModelFactory, CustomRelationalScaffoldingModelFactory>();
    }
}

public class CustomRelationalScaffoldingModelFactory : RelationalScaffoldingModelFactory
{
    public CustomRelationalScaffoldingModelFactory(
        IOperationReporter reporter,
        ICandidateNamingService candidateNamingService,
        IPluralizer pluralizer,
        ICSharpUtilities cSharpUtilities,
        IScaffoldingTypeMapper scaffoldingTypeMapper,
        LoggingDefinitions loggingDefinitions,
        IModelRuntimeInitializer modelRuntimeInitializer)
        : base(
            reporter,
            candidateNamingService,
            pluralizer,
            cSharpUtilities,
            scaffoldingTypeMapper,
            loggingDefinitions,
            modelRuntimeInitializer)
    {
    }
    
    protected override TypeScaffoldingInfo? GetTypeScaffoldingInfo(DatabaseColumn column)
    {
        var typeScaffoldingInfo = base.GetTypeScaffoldingInfo(column);

        // Use any logic you want, to determine the true target CLR type of the
        // property.
        //
        // For this sample code, we assume that the target CLR type has been
        // specified in the comment of the column of the database table,
        // e.g. like: System.Int32[]
        if (typeScaffoldingInfo is not null &&
            column.StoreType == "json" &&
            !string.IsNullOrEmpty(column.Comment))
        {
            var clrTypeName = column.Comment;
            var clrType = Type.GetType(clrTypeName) ?? typeof(string);

            // Regenerate the TypeScaffoldingInfo based on our new CLR type.
            typeScaffoldingInfo = new TypeScaffoldingInfo(
                clrType,
                typeScaffoldingInfo.IsInferred,
                typeScaffoldingInfo.ScaffoldUnicode,
                typeScaffoldingInfo.ScaffoldMaxLength,
                typeScaffoldingInfo.ScaffoldFixedLength,
                typeScaffoldingInfo.ScaffoldPrecision,
                typeScaffoldingInfo.ScaffoldScale);

            // Remove the comment, so that it does not popup in the generated
            // C# source file.
            column.Comment = null;
        }

        return typeScaffoldingInfo;
    }
}

We can use a simple database like the following:

drop database if exists `So73086923_01`;
create database `So73086923_01`;
use `So73086923_01`;

CREATE TABLE `user_chapter` (
  `user_id` int NOT NULL,
  `chapter_id` int NOT NULL,
  `chests` json NOT NULL comment 'System.Int32[]',
  PRIMARY KEY (`user_id`,`chapter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Now scaffold the database:

dotnet ef dbcontext scaffold 'server=127.0.0.1;port=3306;user=root;password=;database=So73086923_01' 'Pomelo.EntityFrameworkCore.MySql' --context 'Context' --verbose --force

A class like the following has now been generated, that uses the correct CLR type for the Chests property:

public partial class UserChapter
{
    public int UserId { get; set; }
    public int ChapterId { get; set; }
    public int[] Chests { get; set; }
}

To use the generated classes in your app, add a UseNewtonsoftJson() call to your context configuration code:

public partial class Context : DbContext
{
    // ...

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
            optionsBuilder.UseMySql(
                "server=127.0.0.1;port=3306;user=root;database=So73086923_01",
                Microsoft.EntityFrameworkCore.ServerVersion.Parse("8.0.25-mysql"),
                builder => builder.UseNewtonsoftJson()) // <-- initialize JSON support
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableDetailedErrors()
                .EnableSensitiveDataLogging();
        }
    }

    // ...
}

You can now use your context:

private static void Main()
{
    // We first clear the `user_chapter` table and then populate it with some test rows.
    using (var context = new Context())
    {
        context.UserChapters.RemoveRange(context.UserChapters.ToList());
        
        context.UserChapters.AddRange(
            new UserChapter { ChapterId = 1, UserId = 1, Chests = new[] { 1, 2, 3 } },
            new UserChapter { ChapterId = 1, UserId = 2, Chests = new[] { 4, 5, 6 } },
            new UserChapter { ChapterId = 2, UserId = 2, Chests = new[] { 7, 8, 9 } });

        context.SaveChanges();
    }

    using (var context = new Context())
    {
        var chapters = context.UserChapters
            .OrderBy(c => c.ChapterId)
            .ThenBy(c => c.UserId)
            .ToList();
        
        Trace.Assert(chapters.Count == 3);
        Trace.Assert(chapters[1].Chests[1] == 5);
    }
}

As long as your properties are declared to use the json database type, Pomelo will serialize/deserialize them for you as JSON.