数据库优先编码优先EF-迁移键和约束名称与数据库不匹配 [英] Database First to Code First EF - Migrations Key and Constraint Names not matching DB
问题描述
我最近使用以下方法将项目从数据库优先更新为代码优先模型：
因此，这是该工具为 DriverScorecardSetting生成的实体表。
[Table（ DriverScorecardSetting）]
公共局部类DriverScorecardSetting
{
[Key]
[DatabaseGenerated（DatabaseGeneratedOption.None）]
public int iCompanyId {get;组; }
个公共虚拟公司Company {get;组; }
....
}
现在我想更新关系并使其成为 1-N 关系。即1家公司有很多 DriverScorecardSetting 。
所以我添加了一个PK并将关系转换为1-N。
[Table（ DriverScorecardSetting）]
公共局部类DriverScorecardSetting
{
[Key]
public int iDriverScorecardSettingId {get;组; }
[ForeignKey（ Company）]
public int iCompanyId {get;组; }
公共虚拟公司Company {组; }
...
}
我也在公司实体。
问题是当我添加迁移文件时。密钥的名称与数据库中的现有密钥不同。因此，当我运行迁移时，它无法在数据库中找到该名称，也不会删除它们。
这是它创建的迁移。
公共部分类PKForDriverScorecardSetting：DbMigration
{
public overlay 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，级联删除：true）;
}
公共重写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）;
}
}
当我在Package Manager控制台中运行此迁移时，错误，因为EF生成的约束名称不正确。这是生成的脚本。
IF object_id（N'[dbo]。[FK_dbo.DriverScorecardSetting_dbo.Companies_iCompanyId]'，N' F'）不是空
ALTER TABLE [dbo]。[DriverScorecardSetting] DROP CONSTRAINT [FK_dbo.DriverScorecardSetting_dbo.Companies_iCompanyId]
ALTER TABLE [dbo]。[DriverScorecardSetting] DROP CONSTRAINT [PK_dbo.DriverS $ b更改表[dbo]。[DriverScorecardSetting]添加[iDriverScorecardSettingId] [int]非空身份
更改表[dbo]。[DriverScorecardSetting]添加约束[PK_dbo.DriverScorecardSetting]主键（[iDriverScore] Id $ b更改表[dbo]。[DriverScorecardSetting]添加约束[FK_dbo.DriverScorecardSetting_dbo.Companies_iCompanyId]外键（[iCompanyId]）参考[dbo]。[公司]（[iCompanyId]）在删除级联
但约束的初始名称不包括。和 dbo 。
现在我知道也许可以通过编码FK约定来解决此问题
我正在使用EF v6.2。
这是现有数据库工作流的代码优先，在使用现有数据库进行代码优先迁移-需要注意的事情 EF6文档的部分：
默认/计算出的名称可能与现有架构不匹配
迁移在支持迁移时会显式指定列和表的名称。但是，还有其他数据库对象，迁移会在应用迁移时计算出默认名称。这包括索引和外键约束。针对现有模式时，这些计算出的名称可能与数据库中实际存在的名称不匹配。
，建议的解决方案是手动编辑生成的迁移代码，并使用可选的 name 参数（如另一个答案所述）：
如果模型中将来的更改需要更改或删除名称不同的数据库对象之一，则需要修改支架式迁移以指定正确的名称。 Migrations API具有一个可选的Name参数，您可以通过该参数执行此操作。例如，您现有的架构可能具有一个Post表，该表具有一个BlogId外键列，该列具有名为IndexFk_BlogId的索引。但是，默认情况下，Migrations希望该索引名为IX_BlogId。如果您对模型进行更改以导致删除该索引，则需要修改脚手架的DropIndex调用以指定IndexFk_BlogId名称。
当然没有人愿意手动执行此操作。不幸的是，正如我在对 EF6中的唯一索引约定的回答中提到的那样，则PK和FK约束名称的问题在于EF6没有用于控制它们的元数据项/属性/注释。如果有这种方法，则逆向工程过程很可能会使用它。但是为了百分百确定，我已经检查了源代码，尽管 ForeignKeyOperation 和 PrimaryKeyOperation 都具有可设置的属性 Name ，除脚手架迁移调用外，其他任何操作均未指定。
很快，约定的想法就死了。还有什么可以做的？好吧，虽然我们无法使用元数据来控制它，但幸运的是，我们可以通过自定义 MigrationCodeGenerator 类：
为生成基于代码的迁移的代码的提供程序的基类。
C＃，我们将继承 CSharpMigrationCodeGenerator ，覆盖生成方法，对每个 ForeignKeyOperation 和 PrimaryKeyOperation应用命名约定>让基地做剩下的事情。示例实现可能是这样的：
using System;
使用System.Collections.Generic;
使用System.Data.Entity.Migrations.Design;
使用System.Data.Entity.Migrations.Model;
使用System.Data.Entity.Migrations.Utilities;
使用System.Linq;
类CustomMigrationCodeGenerator：CSharpMigrationCodeGenerator
{
公共重写ScaffoldedMigration Generate（字符串migrationId，IEnumerable< MigrationOperation>操作，字符串sourceModel，字符串targetModel，字符串@namespace，字符串className）
{
foreach（操作中的var fkOperation.OfType< ForeignKeyOperation>（）
.Where（op => op.HasDefaultName））
{
fkOperation.Name = fkOperation .Name.Replace（ dbo。，）;
//或使用DependentTable，PrincipalTable和DependentColumns属性生成FK名称，
//从表名称中删除架构
}
foreach（操作中的pkOperation。OfType< PrimaryKeyOperation> ;（）
.Concat（operations.OfType< CreateTableOperation>（）。Select（op => op.PrimaryKey））
.Where（op => op.HasDefaultName））
{
pkOperation.Name = pkOperation.Name.Replace（ dbo。，）;
//或使用Table和Columns属性生成PK名称，
//如有必要，从表名称中删除架构
}
返回base.Generate（migrationId，operations，sourceModel，targetModel ，@namespace，className）;
}
受保护的重写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）;
// {基本实现
if（！addForeignKeyOperation.HasDefaultName）
{
writer.Write（ ，name：）;
writer.Write（Quote（addForeignKeyOperation.Name））;
}
//}
writer.Write（））;
}
}
请注意，我们还需要覆盖（替换） GenerateInline（AddForeignKeyOperation 方法的基本实现（在FK作为create table操作的一部分创建时使用），因为当前它有一个忽略<$ c的错误$ c>名称属性（请参见代码中的注释）。
一旦执行此操作，您只需替换标准迁移代码即可。通过设置您的 DbMigrationsConfiguration 派生类构造函数中的CodeGenerator 属性：
内部密封类Configuration：DbMigrationsConfiguration< MyDbContext>
{
public Configuration（）
{
CodeGenerator = new CustomMigrationCodeGenerator（）;
// ...
}
}
I recently updated my project from Database First to a Code First Model, using this method: Link
Everything seemed to be working until I wanted to update my FK and PKs on an existing table.
This was a 1-0, 1-1 relationship. So the PK of Company table was the FK and PK of the DriverScorecardSettingtable.
So this is the entity that the Tool generated for the DriverScorecardSetting table.
[Table("DriverScorecardSetting")]
public partial class DriverScorecardSetting
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int iCompanyId { get; set; }
public virtual Company Company { get; set; }
....
}
Now I want to update the relationship and make it a 1-N relationship. i.e. 1 company many DriverScorecardSetting.
So I added a PK and converted the relationship to 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; }
...
}
I've also made the changes in the company Entity.
The problem is when I'm adding a Migration. The names of the Keys are not the same as the Existing Keys in the DB. So when I run the migration it can't find the name in the DB and is not dropping them.
This is the migration it created.
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");
}
}
When I run this migration in Package Manager Console I get Errors, because the name of the Constraint generated by EF is wrong. This is the Script generated.
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
But the initial names for the Constraints don't include the . and dbo.
Now I know there are maybe a way to solve this by coding a FK Convention Link , But how I do I rename the Convention Name? It is an internal set property only.
I'm using EF v6.2.
This is a known issue with the Code First to an Existing Database workflow, explained in the Code First Migrations with an existing database - Things to be aware of section of the EF6 documentation:
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.
and the suggested solution is to manually edit the generated migration code and utilize the optional name argument (as mentioned by another answer):
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.
Of course no one would like to do that manually. Unfortunately, as I mentioned in my answer to Unique Indexes convention in EF6, the problem with PK and FK constraint names is that EF6 has no metadata item/property/annotation for controlling them. If there was such a way, most likely the reverse engineer process would have used it. But in order to be hundred percent sure, I've checked the source code, and although both ForeignKeyOperation and PrimaryKeyOperation have a settable property Name, it's not specified by any other operation than the scaffolded migration calls.
Shortly, the convention idea is dead. What else can be done? Well, while we can't control that with the metadata, fortunately we can control the migration code generation via custom MigrationCodeGenerator class:
Base class for providers that generate code for code-based migrations.
Since this is C#, We will inherit CSharpMigrationCodeGenerator, override the Generate method, apply our naming convention on each ForeignKeyOperation and PrimaryKeyOperation and let the base do the rest. The sample implementation could be like this:
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(")");
}
}
Note that we need also to override (replace) the base implementation of the GenerateInline(AddForeignKeyOperation method (which is used when the FK is created as part of the create table operation) because currently it has a bug which ignores the Name property (see the comments in code).
Once you do that, all you need is to replace the standard migration code generator by setting the CodeGenerator property inside your DbMigrationsConfiguration derived class constructor:
internal sealed class Configuration : DbMigrationsConfiguration<MyDbContext>
{
public Configuration()
{
CodeGenerator = new CustomMigrationCodeGenerator();
// ...
}
}
这篇关于数据库优先编码优先EF-迁移键和约束名称与数据库不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
