vb.net entity-framework-6 ef-code-first entity-framework-migrations

How does one override the default SQL migration generator?

I'm attempting to override the default behavior of the SQL migrations generator so that I may specify a custom foreign key constraint name, as discussed here. I've wired up the configuration as advised.

Unfortunately, however, it's not going so well.

A quick logging statement reveals that the GetFkName() function is never hit.

I tried an alternate configuration construct, as discussed here and here, but I'm getting this error when I attempt to generate a migration:

More than one migrations configuration type was found in the assembly 'ConsoleApp1'. Specify the name of the one to use.

I find this result a bit odd, as I have only one configuration class, one SQL generation class, and one context class (the code below doesn't reflect this, but I commented out the extras for my actual tests). Specifying the configuration type on the command line, as indicated here, errors with this:

System.InvalidOperationException: The type 'ConsoleApp1.Db.CustomDbConfiguration2' does not inherit from 'System.Data.Entity.DbConfiguration'. Entity Framework code-based configuration classes must inherit from 'System.Data.Entity.DbConfiguration'.

All of this brings us back here, then, which doesn't work for the aforementioned reason (GetFkName() never gets hit). So it seems I'm chasing my tail (didn't know I had one until today).

What should I do to get this override to work correctly?

Configuration

Imports System.Data.Entity
Imports System.Data.Entity.Migrations
Imports System.Data.Entity.SqlServer

Namespace Db
  Friend Class CustomDbConfiguration
    Inherits DbConfiguration

    Public Sub New()
      Me.SetMigrationSqlGenerator(SqlProviderServices.ProviderInvariantName, Function() New CustomSqlGenerator)
    End Sub
  End Class

  Friend Class CustomDbConfiguration2
    Inherits DbMigrationsConfiguration(Of Context)

    Public Sub New()
      Me.SetSqlGenerator(SqlProviderServices.ProviderInvariantName, New CustomSqlGenerator2(Me.GetSqlGenerator(SqlProviderServices.ProviderInvariantName)))
      Me.ContextType = GetType(Context)
    End Sub
  End Class
End Namespace

SQL Generator

Imports System.Data.Entity.Migrations.Model
Imports System.Data.Entity.Migrations.Sql
Imports System.Data.Entity.SqlServer

Namespace Db
  Friend Class CustomSqlGenerator
    Inherits SqlServerMigrationSqlGenerator

    Protected Overrides Sub Generate(AddForeignKeyOperation As AddForeignKeyOperation)
      AddForeignKeyOperation.Name = GetFkName(AddForeignKeyOperation.PrincipalTable, AddForeignKeyOperation.DependentTable, AddForeignKeyOperation.DependentColumns.ToArray())
      MyBase.Generate(AddForeignKeyOperation)
    End Sub

    Protected Overrides Sub Generate(DropForeignKeyOperation As DropForeignKeyOperation)
      DropForeignKeyOperation.Name = GetFkName(DropForeignKeyOperation.PrincipalTable, DropForeignKeyOperation.DependentTable, DropForeignKeyOperation.DependentColumns.ToArray())
      MyBase.Generate(DropForeignKeyOperation)
    End Sub

    Private Shared Function GetFkName(PrimaryKeyTable As String, ForeignKeyTable As String, ParamArray ForeignTableFields As String()) As String
      IO.File.WriteAllText("D:\Logs\FkNameTest.log", $"{Now.ToString}{vbCrLf}")

      Return $"FK_{ForeignKeyTable}_{PrimaryKeyTable}"
    End Function
  End Class

  Friend Class CustomSqlGenerator2
    Inherits MigrationSqlGenerator

    Public Sub New(Generator As MigrationSqlGenerator)
      Me.Generator = Generator
    End Sub

    Public Overrides Function Generate(MigrationOperations As IEnumerable(Of MigrationOperation), ProviderManifestToken As String) As IEnumerable(Of MigrationStatement)
      Return Me.Generator.Generate(MigrationOperations, ProviderManifestToken)
    End Function

    Private ReadOnly Generator As MigrationSqlGenerator
  End Class
End Namespace

Context

Imports System.Data.Common
Imports System.Data.Entity
Imports System.Data.SqlClient
Imports System.Reflection

Namespace Db
  <DbConfigurationType(GetType(CustomDbConfiguration2))>
  Friend Class Context
    Inherits DbContext

    Public Sub New()
      MyBase.New(DbConnection.ConnectionString)
    End Sub

    Private Sub New(Connection As DbConnection)
      MyBase.New(Connection, True)

      Database.SetInitializer(New CreateDatabaseIfNotExists(Of Context))
      Database.SetInitializer(New MigrateDatabaseToLatestVersion(Of Context, Migrations.Configuration))

      Me.Database.Initialize(False)
    End Sub

    Public Shared Function Create() As Context
      Return New Context(DbConnection)
    End Function

    Private Shared ReadOnly Property DbConnection As SqlConnection
      Get
        Return New SqlConnection(Utils.DbConnectionString)
      End Get
    End Property

    Protected Overrides Sub OnModelCreating(Builder As DbModelBuilder)
      Builder.Configurations.AddFromAssembly(Assembly.GetExecutingAssembly)
      MyBase.OnModelCreating(Builder)
    End Sub

    Public Property Documents As DbSet(Of Document)
    Public Property Sections As DbSet(Of Section)
  End Class
End Namespace

Solution

Disclaimer: I haven't coded in VB for many years, these code examples are my feeble attempt to translate my working example in C# into OPs native VB. Please feel free to update my syntax ;)

You can manually edit the migration scripts to specify a custom name for each ForeignKey by specifying a value for the optional Name parameter in the call to Tablebuilder.ForeignKey as part of a create table statement:

CreateTable(
  "dbo.CorporationVariety",
  Function(c) New With
    {
      .Id = c.Int(nullable: false, identity:= true),
      .CorporationId = c.Int(nullable:= false),
      .VarietyId = c.Int(nullable:= false),
    }) _
  .PrimaryKey(Function(t) t.Id)
  .ForeignKey("dbo.Corporation", Function(t) t.CorporationId, name := "FKCorporatationCorporationVarietyCorporationId")
  .ForeignKey("dbo.Variety", Function(t) t.VarietyId, name := "FKVarietyCorporationVarietyVarietyId")
  .Index(Function(t) t.CorporationId)
  .Index(Function(t) t.VarietyId)

Or as part of a DbMigration.AddForeignKey statement:

AddForeignKey("dbo.CorporationVariety", "CorporationId", "dbo.Corporation", name := "FKCorporatationCorporationVarietyCorporationId")
AddForeignKey("dbo.CorporationVariety", "VarietyId", "dbo.Variety", name := "FKVarietyCorporationVarietyVarietyId")

If you have a lot of keys in your Model, and you want to implement a specific convention, (as in a standard rule or sequence of code that you want to apply in given scenarios) across all keys, then Normally the first place to look for a solution is EF Code First Conventions.

Unfortunately, there is neither a standard convention that can help you here nor can you define a custom name for a foreign key using fluent notation...

Normally we would go ahead and create a Custom Code First Convention to define your custom logic, this works in generally 2 ways:

Your convention executes standard configuration via Fluent Notation

we already noted that this option is not available to us...

Your convention logic stores custom metadata to the model via annotations

Primary and Foreign keys seem to be an anomaly in the EF Code First Runtime, there does not seem to be a way to easily access the annotations from the associations even though they are relatively easy to define.

I was surprised to find this and stumbled across this post that further confirms this: https://stackoverflow.com/a/54369685/1690217

Update I started this post assuming that Conventions was the right way to go, because I use it for many other customisations that I've needed to apply over the years. If you are looking to implement other similar types of customisations, look to Conventions first.

We can still easily override the standard VisualBasicMigrationCodeGenerator that generates the migration code files, so lets jump straight into that. coapply to custom name for your ForeignKey and then implement a custom MigrationCodeGenerator to process the output from your convention.

Create a custom VisualBasicMigrationCodeGenerator
Register the Code Generator so that it is used by EF to generate the next migration

NOTE: This will not force existing keys in your database to be renamed. To do that you would need to force each key to be dropped and re-added back. For a large model Consider using a T4 template to create custom once-off migration logic to achieve this, once the above steps are in place.

Think of your Custom VisualBasicMigrationCodeGenerator as your personal EF code first sour dough culture, you can share this and re-use it for every new application, adding new functionality and improvements with each iteration. But Conventions are the configuration options that you may not want in every project, (which is why using _Conventions_ for OPs solution was my first direction.)

1. Create a custom VisualBasicMigrationCodeGenerator

Create a new class that inherits from the EF VisualBasicMigrationCodeGenerator, the minimal we need to do is override the AddForeignKeyOperation and modify the Name of the key and call the base implementation. This will affect all new keys added to the model.

To target keys added as part of CreateTable we will have to override GenerateInline(AddForeignKeyOperation...), however the base implemention (in the C# Generator...) doesn't obey the custom Name so instead we have to replace the implementation entirely.

When doing this, goto the EF project on GitHub and start with the original implementation, then inject your customizations as needed.

Please excuse this C#, I didn't have time to translate it, it does generate the correct VB code though ;)

public class CustomVBMigrationCodeGenerator : System.Data.Entity.Migrations.Design.VisualBasicMigrationCodeGenerator
{

    protected override void Generate(AddForeignKeyOperation addForeignKeyOperation, IndentedTextWriter writer)
    {
        ApplyCustomFKName(addForeignKeyOperation);
        base.Generate(addForeignKeyOperation, writer);
    }

    private void ApplyCustomFKName(ForeignKeyOperation operation)
    {
        // expecting FK without scheme or underscores: "FK{DependentTable}{PrincipalTable}{FKField}"
        operation.Name = $"FK{StripSchemeFromName(operation.DependentTable)}{StripSchemeFromName(operation.PrincipalTable)}{String.Join("", operation.DependentColumns)}";
    }
    private string StripSchemeFromName(string dbObjectName)
    {
        return dbObjectName.Split(new[] { '.' }, 2).Last();
    }

    /// <summary>
    /// Generates code to perform an <see cref="AddForeignKeyOperation" /> as part of a <see cref="CreateTableOperation" />.
    /// </summary>
    /// <param name="addForeignKeyOperation"> The operation to generate code for. </param>
    /// <param name="writer"> Text writer to add the generated code to. </param>
    protected virtual void GenerateInline(AddForeignKeyOperation addForeignKeyOperation, IndentedTextWriter writer)
    {
        // sourced from https://github.com/aspnet/EntityFramework6/blob/master/src/EntityFramework/Migrations/Design/VisualBasicMigrationCodeGenerator.cs
        Check.NotNull(addForeignKeyOperation, "addForeignKeyOperation");
        Check.NotNull(writer, "writer");

        writer.WriteLine(" _");
        writer.Write(".ForeignKey(" + Quote(addForeignKeyOperation.PrincipalTable) + ", ");
        Generate(addForeignKeyOperation.DependentColumns, writer);

        // Our Custom logic
        ApplyCustomFKName(addForeignKeyOperation);

        // Insert our custom name if provided
        if (!addForeignKeyOperation.HasDefaultName)
        {
            writer.Write(", name := " + Quote(addForeignKeyOperation.Name));
        }

        if (addForeignKeyOperation.CascadeDelete)
        {
            writer.Write(", cascadeDelete := True");
        }

        writer.Write(")");
    }
}

2. Register the Code Generator so that it is used by EF to generate the next migration

Locate Configuration.vb in your project, int the constructor set the CodeGenerator to an instance of your CustomVBMigrationCodeGenerator :

Public Sub New()

  AutomaticMigrationsEnabled = false
  CodeGenerator = new CustomVBMigrationCodeGenerator()

End Sub

Now execute the add-migration to generate a new migration you will see you new custom name defined in the migration script.

You may be required to similarly override the Generate(DropForeignKeyOperation...) methods as well if you need to downgrade from this configuration _or_ if alter table commands require the key to be dropped first.