如何让EF迁移为枚举种子数据生成单独InsertData语句以支持SQL Server 2005？

背景 #

我有枚举类型FooNames，需要将其值作为种子数据填充到关联的数据库表Foo中。实体类Foo和枚举FooNames的定义如下：

实体类Foo #

class Foo
{
    [Key]
    [Index("pk")]
    [Column("id")]
    [DatabaseGenerated(DatabaseGeneratedOption.None)] // 强制EF迁移不生成自增列
    public int Id { get; set; }

    [Required]
    [Column("description", TypeName = "varchar")]
    [MaxLength(50)]
    public string Description { get; set; }
}

枚举FooNames #

enum FooNames
{
    Created = 1,
    Pending,
    Approved,
    Reconciled,
    Cancelled,
    Failed,
    Error
}

在DbContext的OnModelCreating()方法中，我通过循环枚举值添加种子数据：

foreach (FooNames name in Enum.GetValues(typeof(FooNames)))
{
    modelBuilder.Entity<Foo>().HasData(new Foo
    {
        Id = (int)name,
        Description = name.ToString()
    });
}

但EF迁移生成的是单条多行INSERT语句，而SQL Server 2005不支持这种语法：

migrationBuilder.InsertData(
        table: "Foo",
        columns: new[] { "id", "description" },
        values: new object[,]
        {
            { 1, "Created" },
            { 2, "Pending" },
            { 3, "Approved" },
            { 4, "Reconciled" },
            { 5, "Cancelled" },
            { 6, "Failed" },
            { 7, "Error" }
        });

需要让EF为每个枚举值生成独立的migrationBuilder.InsertData()调用。

解决方案 #

方法1：手动修改迁移文件（简单直接） #

生成迁移文件后，将自动生成的多行InsertData代码拆分为多个独立的InsertData调用，每个对应一个枚举值：

// 每个枚举值对应一条InsertData
migrationBuilder.InsertData(
    table: "Foo",
    columns: new[] { "id", "description" },
    values: new object[] { 1, "Created" });

migrationBuilder.InsertData(
    table: "Foo",
    columns: new[] { "id", "description" },
    values: new object[] { 2, "Pending" });

migrationBuilder.InsertData(
    table: "Foo",
    columns: new[] { "id", "description" },
    values: new object[] { 3, "Approved" });

migrationBuilder.InsertData(
    table: "Foo",
    columns: new[] { "id", "description" },
    values: new object[] { 4, "Reconciled" });

migrationBuilder.InsertData(
    table: "Foo",
    columns: new[] { "id", "description" },
    values: new object[] { 5, "Cancelled" });

migrationBuilder.InsertData(
    table: "Foo",
    columns: new[] { "id", "description" },
    values: new object[] { 6, "Failed" });

migrationBuilder.InsertData(
    table: "Foo",
    columns: new[] { "id", "description" },
    values: new object[] { 7, "Error" });

这种方式无需额外代码修改，适合一次性需求。

方法2：自定义迁移SQL生成器（长期兼容） #

如果需要长期适配SQL Server 2005，避免每次手动修改迁移文件，可以自定义迁移SQL生成器，强制生成单条INSERT语句：

1. 创建自定义生成器类

using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Sql;

public class SqlServer2005MigrationsSqlGenerator : SqlServerMigrationsSqlGenerator
{
    public SqlServer2005MigrationsSqlGenerator(MigrationsSqlGeneratorDependencies dependencies)
        : base(dependencies)
    {
    }

    protected override void Generate(InsertDataOperation operation)
    {
        // 遍历每个种子数据，生成独立的INSERT语句
        foreach (var valueSet in operation.Values)
        {
            var singleInsert = new InsertDataOperation
            {
                Table = operation.Table,
                Schema = operation.Schema,
                Columns = operation.Columns,
                Values = new[] { valueSet }
            };
            base.Generate(singleInsert);
        }
    }
}

2. 注册自定义生成器

在DbContext的OnConfiguring方法中替换默认的迁移SQL生成器：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer("你的数据库连接字符串")
                  .ReplaceService<IMigrationsSqlGenerator, SqlServer2005MigrationsSqlGenerator>();
}

之后生成的迁移文件会自动为每个种子数据生成独立的InsertData调用，无需手动调整。

内容的提问来源于stack exchange，提问作者Codesmith