Entity Framework 6: How to override SQL generator?

Question

I'd like to amend the SQL that's being generated by EF:CF when generating the database schema (DDL), as suggested by the Entity Framework team.

How can this be done?

I couldn't find anything appropriate via Google.

Answer 1

You can override the MigrationSqlGenerator that is used by Entity Framework by calling the DbMigrationsConfiguration.SetSqlGenerator() method in the constructor of your DbMigrationsConfiguration class, passing the database provider name (e.g. "System.Data.SqlClient" for SQL Server), and the MigrationSqlGenerator instance to use for that database provider.

Consider the example from the work item that you linked to:

public class MyEntity
{
    public int Id { get; set; }

    [Required]
    [MinLength(5)]
    public string Name { get; set; }
}

Suppose that the table for MyEntity had already been generated and the Add-Migration command was used to add the Name field.

By default, the scaffolded migration is:

public partial class AddMyEntity_Name : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.MyEntity", "Name", c => c.String(nullable: false));
    }

    public override void Down()
    {
        DropColumn("dbo.MyEntity", "Name");
    }
}

Notice that the scaffolder did not generate anything for the MinLengthAttribute.

To have EF convey the minimum length requirement, you can specify an attribute-to-column annotation convention. As mentioned on that documentation page, any AnnotationValues are ignored by the default SQL generators.

Within your DbContext's OnModelCreating() override, add the following:

modelBuilder.Conventions.Add(new AttributeToColumnAnnotationConvention<MinLengthAttribute, Int32>("minLength", (property, attributes) => attributes.Single().Length));

After adding that, you can regenerate the scaffolded migration by running Add-Migration -Force AddMyEntity_Name. Now the scaffolded migration is:

public partial class AddMyEntity_Name : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.MyEntity", "Name", c => c.String(nullable: false,
            annotations: new Dictionary<string, AnnotationValues>
            {
                { 
                    "minLength",
                    new AnnotationValues(oldValue: null, newValue: "5")
                },
            }));
    }

    public override void Down()
    {
        DropColumn("dbo.MyEntity", "Name",
            removedAnnotations: new Dictionary<string, object>
            {
                { "minLength", "5" },
            });
    }
}

Suppose that, as in the linked work item, you want to generate a constraint to check that the trimmed Name value is greater than the minLength (5 in this case).

You can start by creating a custom MigrationSqlGenerator that extends SqlServerMigrationSqlGenerator and call SetSqlGenerator() to install the custom MigrationSqlGenerator:

internal class CustomSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(AddColumnOperation addColumnOperation)
    {
        base.Generate(addColumnOperation);
    }
}

internal sealed class Configuration : DbMigrationsConfiguration<DataContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;

        SetSqlGenerator("System.Data.SqlClient", new CustomSqlServerMigrationSqlGenerator());
    }

    protected override void Seed(DataContext context)
    {
        //...
    }
}

Right now, this CustomSqlServerMigrationSqlGenerator overrides the Generate(AddColumnOperation) method, but simply calls the base implementation.

If you look at the documentation of AddColumnOperation, you will see two important properties, Column and Table. Column is the ColumnModel that was created by the lambda in Up(), c => c.String(nullable: false, annotations: ...).

In the Generate() method, you can access the custom AnnotationValues via the Annotations property of the ColumnModel.

To generate the DDL that adds the constraint, you need to generate the SQL and call the Statement() method. For example:

internal class CustomSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(AddColumnOperation addColumnOperation)
    {
        base.Generate(addColumnOperation);

        var column = addColumnOperation.Column;
        if (column.Type == System.Data.Entity.Core.Metadata.Edm.PrimitiveTypeKind.String)
        {
            var annotations = column.Annotations;
            AnnotationValues minLengthValues;
            if (annotations.TryGetValue("minLength", out minLengthValues))
            {
                var minLength = Convert.ToInt32(minLengthValues.NewValue);
                if (minLength > 0)
                {
                    if (Convert.ToString(column.DefaultValue).Trim().Length < minLength)
                    {
                        throw new ArgumentException(String.Format("minLength {0} specified for {1}.{2}, but the default value, '{3}', does not satisfy this requirement.", minLength, addColumnOperation.Table, column.Name, column.DefaultValue));
                    }

                    using (var writer = new StringWriter())
                    {
                        writer.Write("ALTER TABLE ");
                        writer.Write(Name(addColumnOperation.Table));
                        writer.Write(" ADD CONSTRAINT ");
                        writer.Write(Quote("ML_" + addColumnOperation.Table + "_" + column.Name));
                        writer.Write(" CHECK (LEN(LTRIM(RTRIM({0}))) > {1})", Quote(column.Name), minLength);
                        Statement(writer.ToString());
                    }
                }
            }
        }
    }
}

If you run Update-Database -Verbose, you will see an exception generated by CustomSqlServerMigrationSqlGenerator:

minLength 5 specified for dbo.MyEntity.Name, but the default value, '', does not satisfy this requirement.

To fix this issue, specify a defaultValue in the Up() method that is longer than the minimum length (e.g. "unknown"):

    public override void Up()
    {
        AddColumn("dbo.MyEntity", "Name", c => c.String(nullable: false, defaultValue: "unknown",
            annotations: new Dictionary<string, AnnotationValues>
            {
                { 
                    "minLength",
                    new AnnotationValues(oldValue: null, newValue: "5")
                },
            }));
    }

Now if you re-run Update-Database -Verbose, you will see the ALTER TABLE statement that adds the column and the ALTER TABLE statement that adds the constraint:

ALTER TABLE [dbo].[MyEntity] ADD [Name] [nvarchar](max) NOT NULL DEFAULT 'unknown'
ALTER TABLE [dbo].[MyEntity] ADD CONSTRAINT [ML_dbo.MyEntity_Name] CHECK (LEN(LTRIM(RTRIM([Name]))) > 5)

See also: EF6: Writing Your Own Code First Migration Operations, which shows how to implement a custom migration operation.