有没有办法将 mysql json 脚手架成自定义类型?

Question

表看起来像：

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值将像"[1, 2, 3]".所以我想chests映射到int[]或IList<int>.

但是使用dotnet-ef脚手架

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

我得到的是

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");
});

我可以更改代码手册，将类型更改为int[]并将HasConversion添加到实体选项中。

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));
});

但我不认为这是做这本手册的好方法。是否有一些配置可以让 dotnet-ef 脚手架做到这一点？

Answer 1

下面是两个选项，用于将json存储类型映射到特定的 CLR 类型。

选项 1：将存储类型映射到单个 CLR 类型

如果要在基架时将存储类型映射到属性的单个 CLR 类型，可以执行以下操作：

首先，将 Pomelo 支持的两个 JSON 库之一的引用添加到项目文件中。由于您似乎在示例代码中使用了 Newtonsoft 实现，因此我将假设其余答案使用 JSON 堆栈。

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

然后将以下代码添加到项目中，以挂接到 Pomelo 和 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);
}
}

现在搭建数据库基架：

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

现在已生成如下所示的类，该类对Chests属性使用正确的 CLR 类型：

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

若要在应用中使用生成的类，请向上下文配置代码添加UseNewtonsoftJson()调用：

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();
}
}
// ...
}

您现在可以使用上下文：

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);
}
}

只要您的属性声明为json数据库类型，Pomelo 就会将它们序列化/反序列化为 JSON。

选项 2：将存储类型映射到动态选择的 CLR 类型

如果要在基架时将存储类型动态映射到属性的 CLR 类型，可以执行以下操作：

首先，将 Pomelo 支持的两个 JSON 库之一的引用添加到项目文件中。由于您似乎在示例代码中使用了 Newtonsoft 实现，因此我将假设其余答案使用 JSON 堆栈。

还要确保Microsoft.EntityFrameworkCore.Design包引用的<IncludeAssets>子标记包含compile值：

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

然后将以下代码添加到项目中，以挂钩到 EF Core 的基架模型工厂机制，该机制将读取数据库模型(包含表和列)转换为关系模型(具有实体和属性)：

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;
}
}

我们可以使用如下所示的简单数据库：

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;

现在搭建数据库基架：

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

现已生成如下所示的类，该类对Chests属性使用正确的 CLR 类型：

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

若要在应用中使用生成的类，请向上下文配置代码添加UseNewtonsoftJson()调用：

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();
}
}
// ...
}

您现在可以使用上下文：

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);
}
}

只要您的属性声明为json数据库类型，Pomelo 就会将它们序列化/反序列化为 JSON。