c# - Database First to Code First EF - 迁移键和约束名称与数据库不匹配

Question

我最近使用以下方法将我的项目从数据库优先更新为代码优先模型:Link

在我想更新现有表上的 FK 和 PK 之前，一切似乎都正常。

这是 1-0、1-1 的关系。所以 Company 表的 PK 就是 DriverScorecardSetting 表的 FK 和 PK。

因此，这是该工具为 DriverScorecardSetting 表生成的实体。

[Table("DriverScorecardSetting")]
public partial class DriverScorecardSetting
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int iCompanyId { get; set; }
    public virtual Company Company { get; set; }
 ....
}

现在我想更新关系并使其成为1-N 关系。即 1 个公司有多个 DriverScorecardSetting。

所以我加了一个PK，把关系转换成了1-N。

[Table("DriverScorecardSetting")]
public partial class DriverScorecardSetting
{
    [Key]
    public int iDriverScorecardSettingId { get; set; }


    [ForeignKey("Company")]
    public int iCompanyId { get; set; }

    public virtual Company Company { get; set; }
   ...
 }

我还在公司实体中进行了更改。

问题出在我添加迁移时。 key 的名称与数据库中的现有 key 不同。因此，当我运行迁移时，它无法在数据库中找到名称并且不会删除它们。

这是它创建的迁移。

public partial class PKForDriverScorecardSetting : DbMigration
{
    public override void Up()
    {
        DropForeignKey("dbo.DriverScorecardSetting", "iCompanyId", "dbo.Companies");
        DropPrimaryKey("dbo.DriverScorecardSetting");
        AddColumn("dbo.DriverScorecardSetting", "iDriverScorecardSettingId", c => c.Int(nullable: false, identity: true));
        AddPrimaryKey("dbo.DriverScorecardSetting", "iDriverScorecardSettingId");
        AddForeignKey("dbo.DriverScorecardSetting", "iCompanyId", "dbo.Companies", "iCompanyId", cascadeDelete: true);
    }

    public override void Down()
    {
        DropForeignKey("dbo.DriverScorecardSetting", "iCompanyId", "dbo.Companies");
        DropPrimaryKey("dbo.DriverScorecardSetting");
        DropColumn("dbo.DriverScorecardSetting", "iDriverScorecardSettingId");
        AddPrimaryKey("dbo.DriverScorecardSetting", "iCompanyId");
        AddForeignKey("dbo.DriverScorecardSetting", "iCompanyId", "dbo.Companies", "iCompanyId");
    }
}

当我在程序包管理器控制台中运行此迁移时，出现错误，因为 EF 生成的约束名称是错误的。这是生成的脚本。

IF object_id(N'[dbo].[FK_dbo.DriverScorecardSetting_dbo.Companies_iCompanyId]', N'F') IS NOT NULL
    ALTER TABLE [dbo].[DriverScorecardSetting] DROP CONSTRAINT [FK_dbo.DriverScorecardSetting_dbo.Companies_iCompanyId]
ALTER TABLE [dbo].[DriverScorecardSetting] DROP CONSTRAINT [PK_dbo.DriverScorecardSetting]
ALTER TABLE [dbo].[DriverScorecardSetting] ADD [iDriverScorecardSettingId] [int] NOT NULL IDENTITY
ALTER TABLE [dbo].[DriverScorecardSetting] ADD CONSTRAINT [PK_dbo.DriverScorecardSetting] PRIMARY KEY ([iDriverScorecardSettingId])
ALTER TABLE [dbo].[DriverScorecardSetting] ADD CONSTRAINT [FK_dbo.DriverScorecardSetting_dbo.Companies_iCompanyId] FOREIGN KEY ([iCompanyId]) REFERENCES [dbo].[Companies] ([iCompanyId]) ON DELETE CASCADE

但是约束的初始名称不包括 . 和 dbo.

现在我知道可能有一种方法可以通过编写 FK 约定来解决这个问题 Link ，但是我该如何重命名约定名称呢？它只是一个内部设置属性。

我使用的是 EF v6.2。

Answer 1

这是 Code First to an Existing Database 的一个已知问题工作流程，在 Code First Migrations with an existing database - Things to be aware of 中解释EF6 文档部分:

Default/calculated names may not match existing schema

Migrations explicitly specifies names for columns and tables when it scaffolds a migrations. However, there are other database objects that Migrations calculates a default name for when applying the migrations. This includes indexes and foreign key constraints. When targeting an existing schema, these calculated names may not match what actually exists in your database.

建议的解决方案是手动编辑生成的迁移代码并使用可选的 name 参数(如另一个答案所述):

If future changes in your model require changing or dropping one of the database objects that is named differently, you will need to modify the scaffolded migration to specify the correct name. The Migrations APIs have an optional Name parameter that allows you to do this. For example, your existing schema may have a Post table with a BlogId foreign key column that has an index named IndexFk_BlogId. However, by default Migrations would expect this index to be named IX_BlogId. If you make a change to your model that results in dropping this index, you will need to modify the scaffolded DropIndex call to specify the IndexFk_BlogId name.

当然没有人愿意手动执行此操作。不幸的是，正如我在对 Unique Indexes convention in EF6 的回答中提到的那样，PK 和 FK 约束名称的问题在于 EF6 没有用于控制它们的元数据项/属性/注释。如果有这样的方法，逆向工程过程很可能会使用它。但是为了百分百确定，我检查了源代码，虽然ForeignKeyOperation和PrimaryKeyOperation都有一个可设置的属性Name，它不是由脚手架迁移调用以外的任何其他操作指定的。

很快，公约的想法就死了。还能做什么？好吧，虽然我们无法通过元数据控制它，幸运的是我们可以通过自定义 MigrationCodeGenerator 控制迁移代码生成类:

Base class for providers that generate code for code-based migrations.

由于这是C#，我们将继承CSharpMigrationCodeGenerator , 覆盖 Generate方法，在每个 ForeignKeyOperation 和 PrimaryKeyOperation 上应用我们的命名约定，让 base 完成剩下的工作。示例实现可能是这样的:

using System;
using System.Collections.Generic;
using System.Data.Entity.Migrations.Design;
using System.Data.Entity.Migrations.Model;
using System.Data.Entity.Migrations.Utilities;
using System.Linq;

class CustomMigrationCodeGenerator : CSharpMigrationCodeGenerator
{
    public override ScaffoldedMigration Generate(string migrationId, IEnumerable<MigrationOperation> operations, string sourceModel, string targetModel, string @namespace, string className)
    {
        foreach (var fkOperation in operations.OfType<ForeignKeyOperation>()
            .Where(op => op.HasDefaultName))
        {
            fkOperation.Name = fkOperation.Name.Replace("dbo.", "");
            // or generate FK name using DependentTable, PrincipalTable and DependentColumns properties,
            // removing schema from table names if needed
        }
        foreach (var pkOperation in operations.OfType<PrimaryKeyOperation>()
            .Concat(operations.OfType<CreateTableOperation>().Select(op => op.PrimaryKey))
            .Where(op => op.HasDefaultName))
        {
            pkOperation.Name = pkOperation.Name.Replace("dbo.", "");
            // or generate PK name using Table and Columns properties,
            // removing schema from table name if needed
        }
        return base.Generate(migrationId, operations, sourceModel, targetModel, @namespace, className);
    }

    protected override void GenerateInline(AddForeignKeyOperation addForeignKeyOperation, IndentedTextWriter writer)
    {
        writer.WriteLine();
        writer.Write(".ForeignKey(" + Quote(addForeignKeyOperation.PrincipalTable) + ", ");
        Generate(addForeignKeyOperation.DependentColumns, writer);
        if (addForeignKeyOperation.CascadeDelete)
            writer.Write(", cascadeDelete: true");
        // { missing in base implementation
        if (!addForeignKeyOperation.HasDefaultName)
        {
            writer.Write(", name: ");
            writer.Write(Quote(addForeignKeyOperation.Name));
        }
        // }
        writer.Write(")");
    }
}

请注意，我们还需要覆盖(替换)GenerateInline(AddForeignKeyOperation 方法(在创建 FK 作为创建表操作的一部分时使用)的基本实现，因为当前它具有忽略 Name 属性的错误(请参阅代码中的注释)。

完成后，您只需通过设置 CodeGenerator 来替换标准迁移代码生成器。 DbMigrationsConfiguration 派生类构造函数中的属性:

internal sealed class Configuration : DbMigrationsConfiguration<MyDbContext>
{
    public Configuration()
    {
        CodeGenerator = new CustomMigrationCodeGenerator();
        // ...
    }
}