Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Change foreign key constraint naming convention

We have our own external convention of naming objects and I need to change the naming convention for the auto generated foreign key constraints. Now it looks like: FK_dbo.City_dbo.CityType_City_CityTypeId but I would like it to be called City_FKC_CityType.

I found a similar question which says that you can change the name of constraints manually. However, this does not suit me, since I have a lot of tables and foreign key constraints.

I found some information about "Custom Code First Conventions" and I am wondering if I can change the name of constraint using this or if there are any methods to implement it?

Another variant is download the source code of EF, make changes and use that but that is in case of emergency.

As a side note, I would also like to change the naming convention of the primary key.

like image 882
Roman Marusyk Avatar asked Jul 21 '15 09:07

Roman Marusyk


1 Answers

You can implement a custom sql generator class derived from SqlServerMigrationSqlGenerator from System.Data.Entity.SqlServer namespace:

public class CustomSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(AddForeignKeyOperation addForeignKeyOperation)
    {
        addForeignKeyOperation.Name = getFkName(addForeignKeyOperation.PrincipalTable,
            addForeignKeyOperation.DependentTable, addForeignKeyOperation.DependentColumns.ToArray());
        base.Generate(addForeignKeyOperation);
    }

    protected override void Generate(DropForeignKeyOperation dropForeignKeyOperation)
    {
        dropForeignKeyOperation.Name = getFkName(dropForeignKeyOperation.PrincipalTable,
            dropForeignKeyOperation.DependentTable, dropForeignKeyOperation.DependentColumns.ToArray());
        base.Generate(dropForeignKeyOperation);
    }

    private static string getFkName(string primaryKeyTable, string foreignKeyTable, params string[] foreignTableFields)
    {
        // Return any format you need
    }
}

Then you need to register your generator in DbContext using DbConfiguration:

public class CustomDbConfiguration : DbConfiguration
{
    public CustomDbConfiguration()
    {
        SetMigrationSqlGenerator(SqlProviderServices.ProviderInvariantName,
            () => new CustomSqlGenerator());
    }
}

And DbConfigurationTypeAttribute:

[DbConfigurationType(typeof(CustomDbConfiguration))]
public class YourEntities : DbContext

UPDATE: If you want to change a primary key name, you need to override following Generate methods:

protected override void Generate(CreateTableOperation createTableOperation) 
{
    createTableOperation.PrimaryKey.Name = getPkName(createTableOperation.Name);
    base.Generate(createTableOperation);
}

protected override void Generate(AddPrimaryKeyOperation addPrimaryKeyOperation)
{
    addPrimaryKeyOperation.Name = getPkName(addPrimaryKeyOperation.Table);
    base.Generate(addPrimaryKeyOperation);
}

protected override void Generate(DropPrimaryKeyOperation dropPrimaryKeyOperation)
{
    dropPrimaryKeyOperation.Name = getPkName(dropPrimaryKeyOperation.Table);
    base.Generate(dropPrimaryKeyOperation);
}
like image 102
at least 3 and no more than 30 Avatar answered Oct 20 '22 08:10

at least 3 and no more than 30