Database First to Code First EF - Migrations Key and Constraint Names not matching DB

Question

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.

Answer 1

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

Answer 2

You can modify the Up() and Down() methods in the created migration. Use the overload of DropForeignKey that uses the foreign key name. The DropPrimaryKey also needs to be changed.

public partial class PKForDriverScorecardSetting : DbMigration
{
    public override void Up()
    {
        //DropForeignKey("dbo.DriverScorecardSetting", "iCompanyId", "dbo.Companies"); // different name
        DropForeignKey("dbo.DriverScorecardSetting", "FK_DriverScorecardSetting_Companies"); // drop FK by name

        //DropPrimaryKey("dbo.DriverScorecardSetting"); // different name
        DropPrimaryKey("dbo.DriverScorecardSetting", "PK_DriverScorecardSetting"); // drop PK by name

        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");// different name
        AddPrimaryKey("dbo.DriverScorecardSetting", "iCompanyId", name:"PK_DriverScorecardSetting");// Add PK with name

        //AddForeignKey("dbo.DriverScorecardSetting", "iCompanyId", "dbo.Companies", "iCompanyId");// different name
        AddForeignKey("dbo.DriverScorecardSetting", "iCompanyId", "dbo.Companies", "iCompanyId", name:"FK_DriverScorecardSetting_Companies");// different name
    }
}

Links:

• https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.migrations.dbmigration.dropforeignkey?view=entity-framework-6.2.0#System_Data_Entity_Migrations_DbMigration_DropForeignKey_System_String_System_String_System_Object_
• https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.migrations.dbmigration.dropprimarykey?view=entity-framework-6.2.0#System_Data_Entity_Migrations_DbMigration_DropPrimaryKey_System_String_System_String_System_Object_
• https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.migrations.dbmigration.addforeignkey?view=entity-framework-6.2.0#System_Data_Entity_Migrations_DbMigration_AddForeignKey_System_String_System_String_System_String_System_String_System_Boolean_System_String_System_Object_
• https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.migrations.dbmigration.addprimarykey?view=entity-framework-6.2.0#System_Data_Entity_Migrations_DbMigration_AddPrimaryKey_System_String_System_String_System_String_System_Boolean_System_Object_