How can I stop Entity Framework 5 migrations adding dbo. into key names?

Question

I started a project using Entity Framework 4.3 Code First with manual migrations and SQL Express 2008 and recently updated to EF5 (in VS 2010) and noticed that now when I change something like a foreign key constraint, the migrations code adds the "dbo." to the start of the table name and hence the foreign key name it constructs is incorrect for existing constraints (and in general now seem oddly named).

Original migration script in EF 4.3 (note ForeignKey("Products", t => t.Product_Id)):

    CreateTable(
        "Products",
        c => new
            {
                Id = c.Int(nullable: false, identity: true),
                ProductName = c.String(),
            })
        .PrimaryKey(t => t.Id);

    CreateTable(
        "KitComponents",
        c => new
            {
                Id = c.Int(nullable: false, identity: true),
                Component_Id = c.Int(),
                Product_Id = c.Int(),
            })
        .PrimaryKey(t => t.Id)
        .ForeignKey("Products", t => t.Component_Id)
        .ForeignKey("Products", t => t.Product_Id)
        .Index(t => t.Component_Id)
        .Index(t => t.Product_Id);

Foreign Key names generated: FK_KitComponents_Products_Product_Id FK_KitComponents_Products_Component_Id

If I then upgrade to EF5 and change the foreign key the migration code looks something like (note the "dbo.KitComponents" and "dbo.Products" as opposed to just "KitComponents" and "Products"):

DropForeignKey("dbo.KitComponents", "Product_Id", "dbo.Products");
DropIndex("dbo.KitComponents", new[] { "Product_Id" });

and the update-database fails with the message: 'FK_dbo.KitComponents_dbo.Products_Product_Id' is not a constraint. Could not drop constraint. See previous errors.

so it seems as of EF5 the constraint naming has changed from FK_KitComponents_Products_Product_Id to FK_dbo.KitComponents_dbo.Products_Product_Id (with dbo. prefix)

How can I get EF5 to behave as it was in EF 4.3 so I don't have to alter every piece of new migration code it spits out?

I haven't been able to find any release notes about why this changed and what to do about it :(

In EF5, we stopped stripping dbo off because if your user's default schema was not "dbo" things would break. Now we treat dbo just like any other schema. — bricelam, Aug 21 '12 at 18:37

score 33 · Accepted Answer · edited Feb 18 '15 at 12:38

33

You can customize the generated code by sub-classing the CSharpMigrationCodeGenerator class:

class MyCodeGenerator : CSharpMigrationCodeGenerator
{
    protected override void Generate(
        DropIndexOperation dropIndexOperation, IndentedTextWriter writer)
    {
        dropIndexOperation.Table = StripDbo(dropIndexOperation.Table);

        base.Generate(dropIndexOperation, writer);
    }

    // TODO: Override other Generate overloads that involve table names

    private string StripDbo(string table)
    {
        if (table.StartsWith("dbo."))
        {
            return table.Substring(4);
        }

        return table;
    }
}

Then set it in your migrations configuration class:

class Configuration : DbMigrationsConfiguration<MyContext>
{
    public Configuration()
    {
        CodeGenerator = new MyCodeGenerator();
    }
}

edited Feb 18 '15 at 12:38

albertjan

7,739
6
44
74

answered Aug 21 '12 at 18:34

bricelam

28,825
9
92
117

Many thanks @Brice, this was what I was looking for. I understand the change, but thought EF5 might deal with existing setups a little more elegantly...or at least give a warning. For others reading and wanting EF4 behavior you'll have to override most of the other Generate() functions to strip the dbo. and tweak the StripDbo() function to return a string - as a ref param doesn't work on properties. – mips Aug 22 '12 at 01:53
@mips, I've updated the code accordingly; I was hoping I could change the ref parameter before you read it. ;) – bricelam Aug 22 '12 at 16:25
I am getting the error Error 1 'NiDS.Web.Migrations.MigrationsGenerator.Generate(System.Data.Entity.Migrations.Model.DropIndexOperation, System.CodeDom.Compiler.IndentedTextWriter)': no suitable method found to override c:\users\wayne blackmon\documents\visual studio 2012\Projects\NiDS 2.0\NiDS.Web\Migrations\MigrationsGenerator.cs 9 33 NiDS.Web – wayne.blackmon Oct 28 '12 at 20:46
Wayne, try changing IndentedTextWriter to System.Data.Entity.Migrations.Utilities.IndentedTextWriter? – Patrick McDonald Nov 07 '12 at 14:56
@Brice Hi Brice, does this only work with custom migrations using your own Up method? I've got a similar issue and I tried like in your answer but it doesn't work...http://stackoverflow.com/questions/17241594/entity-framework-code-first-migrations-cannot-drop-constraint-because-it-doesn – SventoryMang Jun 21 '13 at 21:42
@DOTang Yes, this will only work for custom migrations. Automatic migrations are never generated as code. They go straight to the SQL generator. You may be able to intercept there. See [this post](http://romiller.com/2012/01/16/customizing-code-first-migrations-provider/) for more information. – bricelam Jun 21 '13 at 23:02

score 5 · Answer 2 · answered May 14 '14 at 13:53

For Automatic Migrations use this code:

public class MyOwnMySqlMigrationSqlGenerator : MySqlMigrationSqlGenerator
{
    protected override MigrationStatement Generate(AddForeignKeyOperation addForeignKeyOperation)
    {
        addForeignKeyOperation.PrincipalTable = addForeignKeyOperation.PrincipalTable.Replace("dbo.", "");
        addForeignKeyOperation.DependentTable = addForeignKeyOperation.DependentTable.Replace("dbo.", "");
        MigrationStatement ms = base.Generate(addForeignKeyOperation);
        return ms;
    }
}

And Set it on configuration:

SetSqlGenerator("MySql.Data.MySqlClient", new MyOwnMySqlMigrationSqlGenerator());

To help others leave my comment, this answer removed the .dbo when I ran Update-Database in the console package manager, I also used the DropForeignKeyOperation in the same way as the AddForeignKeyOperation, the two worked perfect, thanks for your answer. — Mauricio Ferraz, Jul 25 '18 at 13:35

score 2 · Answer 3 · edited May 23 '17 at 12:25

2

This is a fine answer, however, if you're just looking for a 'quick fix' approach, there's this as well EF Migrations DropForeignKey fails when key is in a base class

Use the DropForeignKey overload which contains the parameters principalName and name -- which in this case means constraint name!

edited May 23 '17 at 12:25

Community

1
1

answered Sep 14 '12 at 22:16

Vinney Kelly

4,975
1
25
31

score 1 · Answer 4 · answered Dec 24 '14 at 19:50

Improving on bricelam's answer, I tried this on EF6. Made a few changes to keep the schema as part of table name and only remove it from the FK or PK name

internal class MyCodeGenerator : CSharpMigrationCodeGenerator
{
    protected override void Generate(AddForeignKeyOperation addForeignKeyOperation, IndentedTextWriter writer)
    {
        addForeignKeyOperation.Name = this.StripDbo(addForeignKeyOperation.Name, addForeignKeyOperation.DependentTable);
        addForeignKeyOperation.Name = this.StripDbo(addForeignKeyOperation.Name, addForeignKeyOperation.PrincipalTable);
        base.Generate(addForeignKeyOperation, writer);
    }

    protected override void Generate(AddPrimaryKeyOperation addPrimaryKeyOperation, IndentedTextWriter writer)
    {
        addPrimaryKeyOperation.Name = StripDbo(addPrimaryKeyOperation.Name, addPrimaryKeyOperation.Table);
        base.Generate(addPrimaryKeyOperation, writer);
    }

    protected override void Generate(DropForeignKeyOperation dropForeignKeyOperation, IndentedTextWriter writer)
    {
        dropForeignKeyOperation.Name = this.StripDbo(dropForeignKeyOperation.Name, dropForeignKeyOperation.DependentTable);
        dropForeignKeyOperation.Name = this.StripDbo(dropForeignKeyOperation.Name, dropForeignKeyOperation.PrincipalTable);
        base.Generate(dropForeignKeyOperation, writer);
    }

    protected override void Generate(DropPrimaryKeyOperation dropPrimaryKeyOperation, IndentedTextWriter writer)
    {
        dropPrimaryKeyOperation.Name = StripDbo(dropPrimaryKeyOperation.Name, dropPrimaryKeyOperation.Table);
        base.Generate(dropPrimaryKeyOperation, writer);
    }

    private string StripDbo(string objectName, string tableName)
    {
        if (tableName.StartsWith("dbo."))
        {
            return objectName.Replace(tableName, tableName.Substring(4));
        }

        return objectName;
    }
}

How can I stop Entity Framework 5 migrations adding dbo. into key names?

4 Answers4

Linked