Named Default Constraints in EF6

If you just want to see the code, skip to the TL;DR section at the bottom.

There are multiple resources on how to set the SQL default value for a column in EF6 Code-First, but none of the resources cover changing the default value constraint’s name to match the standard DF_TableName_ColumnName naming convention. At least for the SqlServerMigrationSqlGenerator class, when EF6 generates the SQL for creating the column it uses this syntax to create the default constraint:

CREATE TABLE Persons (  
    ID int NOT NULL,  
    LastName varchar(255) NOT NULL,  
    FirstName varchar(255),  
    Age int,  
    City varchar(255) DEFAULT 'Sandnes'  
);

This syntax creates randomly named default constraints, looking something like this:

DF__Persons__City__056ECC6A

This is gross — when looking at the constraints on the table, it is unclear what this default constraint applies to, particularly if it has to truncate the column name. We’re forced to dig deeper to find out. This generated name will also be different on every database this query is run on, so if we need to change this default constraint later we have to write an inhumane query that looks at the column metadata to drop the constraint, something like this from stackoverflow:

DECLARE @sqlStatement nvarchar(MAX),
        @tableName nvarchar(50) = 'TripEvent',
        @columnName nvarchar(50) = 'CreatedDate';

SELECT                  
    @sqlStatement = 'ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + dc.name + ';'
FROM            
    sys.default_constraints AS dc
LEFT JOIN   
    sys.columns AS sc ON (dc.parent_column_id = sc.column_id)
WHERE 
    dc.parent_object_id = OBJECT_ID(@tableName)
    AND type_desc = 'DEFAULT_CONSTRAINT'
    AND sc.name = @columnName
PRINT'   ['+@tableName+']:'+@@SERVERNAME+'.'+DB_NAME()+'@'+CONVERT(VarChar, GETDATE(), 127)+';  '+@sqlStatement;
IF (LEN(@sqlStatement) > 0)
     EXEC sp_executesql @sqlStatement

Luckily for us, EF6 was written in a modular way that allows us to override a lot of the SQL generation logic. We can see how the SQL is generated in the EF6 Source Code:

protected internal virtual void Generate(ColumnModel column, IndentedTextWriter writer)
{
    Check.NotNull(column, "column");
    Check.NotNull(writer, "writer");

    writer.Write(Quote(column.Name));
    writer.Write(" ");
    writer.Write(BuildColumnType(column));
    if ((column.IsNullable != null)
        && !column.IsNullable.Value)
    {
        writer.Write(" NOT NULL");
    }
    if (column.DefaultValue != null)
    {
        writer.Write(" DEFAULT ");
        writer.Write(Generate((dynamic)column.DefaultValue));
    }
    else if (!string.IsNullOrWhiteSpace(column.DefaultValueSql))
    {
        writer.Write(" DEFAULT ");
        writer.Write(column.DefaultValueSql);
    }
    else if (column.IsIdentity)
    {
        if ((column.Type == PrimitiveTypeKind.Guid)
            && (column.DefaultValue == null))
        {
            writer.Write(" DEFAULT " + GuidColumnDefault);
        }
        else
        {
            writer.Write(" IDENTITY");
        }
    }
}

Of particular interest is this section of code where the default value constraints are generated:

if (column.DefaultValue != null)
{
    writer.Write(" DEFAULT ");
    writer.Write(Generate((dynamic)column.DefaultValue));
}
else if (!string.IsNullOrWhiteSpace(column.DefaultValueSql))
{
    writer.Write(" DEFAULT ");
    writer.Write(column.DefaultValueSql);
}

So we just need to change this to write the constraint in the format we want, right? We’ve reached the finish line! Except, well, no — this method has no way to access the table name, so we can’t match our constraint naming convention. So we’ll have to override the methods that call this as well, which do have access to the table name hopefully.

So, let’s dive into the source code again and identify the caller methods: In WriteCreateTable(CreateTableOperation createTableOperation, IndentedTextWriter writer) it is called in this block:

createTableOperation.Columns.Each(  
    (c, i) =>  
    {  
        Generate(c, writer);  
        if (i < createTableOperation.Columns.Count - 1)  
        {            
          writer.WriteLine(",");  
        }    
});

In Generate(AddColumnOperation addColumnOperation) it is called in this block:

var column = addColumnOperation.Column;  

Generate(column, writer);

Since both of these methods have access to the table name, we’ll write up a new Generate method that takes a tableName parameter so it can write the default constraint name in the format we want. As you can see, most of the method is identical to the original EF6 method, but we're calling a couple of new helper methods - one sets the SqlDefaultValue annotation, and another takes in the column name and table name and spits out the correct default constraint name.

protected void Generate(ColumnModel column, string tableName, IndentedTextWriter writer)
{
    if (column == null)
    {
        throw new ArgumentNullException(nameof(column));
    }

    if (writer == null)
    {
        throw new ArgumentNullException(nameof(writer));
    }
    SetAnnotatedColumn(column, tableName);
    writer.Write(Quote(column.Name));
    writer.Write(" ");
    writer.Write(BuildColumnType(column));
    if (column.IsNullable != null
        && !column.IsNullable.Value)
    {
        writer.Write(" NOT NULL");
    }
    if (column.DefaultValue != null)
    {
        writer.Write($" CONSTRAINT {GetDefaultConstraintName(column.Name, tableName)} DEFAULT ");
        writer.Write(Generate((dynamic)column.DefaultValue));
    }
    else if (!string.IsNullOrWhiteSpace(column.DefaultValueSql))
    {
        writer.Write($" CONSTRAINT {GetDefaultConstraintName(column.Name, tableName)} DEFAULT ");
        writer.Write(column.DefaultValueSql);
    }
    else if (column.IsIdentity)
    {
        if (column.Type == PrimitiveTypeKind.Guid
            && (column.DefaultValue == null))
        {
            writer.Write(" DEFAULT " + GuidColumnDefault);
        }
        else
        {
            writer.Write(" IDENTITY");
        }
    }
}

There’s a few helper methods that we call as well:

private void SetAnnotatedColumn(ColumnModel column, string tableName)
{
    if (column.Annotations.TryGetValue("SqlDefaultValue", out AnnotationValues values))
    {
        if (values.NewValue == null)
        {
            column.DefaultValueSql = null;
            using (IndentedTextWriter writer = Writer())
            {
                // Drop Constraint
                writer.WriteLine(GetSqlDropConstraintQuery(tableName, column.Name));
                Statement(writer);
            }
        }
        else
        {
            column.DefaultValueSql = (string)values.NewValue;
        }
    }
}

private string GetDefaultConstraintName(string columnName, string tableName)
{
    tableName = tableName.RemoveAllOccurencesOf("dbo.");
    return $"DF_{tableName}_{columnName}";
}

private string GetSqlDropConstraintQuery(string tableName, string columnName)
{
    string[] tableNameSplitByDot = tableName.Split('.');
    string tableSchema = tableNameSplitByDot[0];
    string tablePureName = tableNameSplitByDot[1];
    string str = $@"DECLARE @var{_dropConstraintCount} nvarchar(128)
SELECT @var{_dropConstraintCount} = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'{tableSchema}.[{tablePureName}]')
AND col_name(parent_object_id, parent_column_id) = '{columnName}';
IF @var{_dropConstraintCount} IS NOT NULL
EXECUTE('ALTER TABLE {tableSchema}.[{tablePureName}] DROP CONSTRAINT [' + @var{_dropConstraintCount} + ']')";
    _dropConstraintCount++;
    return str;
}

And then override WriteCreateTable(CreateTableOperation createTableOperation, IndentedTextWriter writer) and Generate(AddColumnOperation addColumnOperation), simply swapping in our new method and making some ancillary changes to remove calls to utility methods we don't have access to:

override protected void WriteCreateTable(CreateTableOperation createTableOperation, IndentedTextWriter writer)
{
    if (createTableOperation == null)
    {
        throw new ArgumentNullException(nameof(createTableOperation));
    }

    if (writer == null)
    {
        throw new ArgumentNullException(nameof(writer));
    }

    writer.WriteLine("CREATE TABLE " + Name(createTableOperation.Name) + " (");
    writer.Indent++;
    createTableOperation.Columns.ForEach(
        (c, i) =>
        {
            Generate(c, createTableOperation.Name, writer);
            if (i < createTableOperation.Columns.Count - 1)
            {
                writer.WriteLine(",");
            }
        });

    if (createTableOperation.PrimaryKey != null)
    {
        writer.WriteLine(",");
        writer.Write("CONSTRAINT ");
        writer.Write(Quote(createTableOperation.PrimaryKey.Name));
        writer.Write(" PRIMARY KEY ");
        if (!createTableOperation.PrimaryKey.IsClustered)
        {
            writer.Write("NONCLUSTERED ");
        }
        writer.Write("(");
        writer.Write(string.Join(", ", createTableOperation.PrimaryKey.Columns.Select(Quote)));
        writer.WriteLine(")");
    }
    else
    {
        writer.WriteLine();
    }

    writer.Indent--;
    writer.Write(")");
}

override protected void Generate(AddColumnOperation addColumnOperation)
{
    if (addColumnOperation == null)
    {
        throw new ArgumentNullException(nameof(addColumnOperation));
    }

    using (IndentedTextWriter writer = Writer())
    {
        writer.Write("ALTER TABLE ");
        writer.Write(Name(addColumnOperation.Table));
        writer.Write(" ADD ");
        ColumnModel column = addColumnOperation.Column;
        Generate(column, addColumnOperation.Table, writer);
        if ((column.IsNullable != null)
            && !column.IsNullable.Value
            && (column.DefaultValue == null)
            && (string.IsNullOrWhiteSpace(column.DefaultValueSql))
            && !column.IsIdentity
            && !column.IsTimestamp
            && !(column.StoreType?.Equals("rowversion", StringComparison.OrdinalIgnoreCase) ?? false)
            && !(column.StoreType?.Equals("timestamp", StringComparison.OrdinalIgnoreCase) ?? false))
        {
            writer.Write(" DEFAULT ");

            if (column.Type == PrimitiveTypeKind.DateTime)
            {
                writer.Write(Generate(DateTime.Parse("1900-01-01 00:00:00", CultureInfo.InvariantCulture)));
            }
            else
            {
                writer.Write(Generate((dynamic)column.ClrDefaultValue));
            }
        }

        Statement(writer);
    }
}

We also need to update the SQL generated when an alter column operation is performed by Generate(AlterColumnOperation alterColumnOperation). It's worth mentioning by default this method does follow the standard naming convention, but it includes the schema (like DF_dbo.TableName_ColumnName) which we don't want, so we'll update it to call our new GetDefaultConstraintName(string, string) method.

override protected void Generate(AlterColumnOperation alterColumnOperation)
{
    SetAnnotatedColumn(alterColumnOperation.Column, alterColumnOperation.Table);
    ColumnModel column = alterColumnOperation.Column;
    using (IndentedTextWriter writer = Writer())
    {
        DropDefaultConstraint(alterColumnOperation.Table, column.Name, writer);
        writer.Write("ALTER TABLE ");
        writer.Write(Name(alterColumnOperation.Table));
        writer.Write(" ALTER COLUMN ");
        writer.Write(Quote(column.Name));
        writer.Write(" ");
        writer.Write(BuildColumnType(column));
        if ((column.IsNullable != null)
            && !column.IsNullable.Value)
        {
            writer.Write(" NOT");
        }

        writer.Write(" NULL");

        if ((column.DefaultValue != null)
            || !string.IsNullOrWhiteSpace(column.DefaultValueSql))
        {
            writer.WriteLine();
            writer.Write("ALTER TABLE ");
            writer.Write(Name(alterColumnOperation.Table));
            writer.Write(" ADD CONSTRAINT ");
            writer.Write(Quote(GetDefaultConstraintName(column.Name, alterColumnOperation.Table)));
            writer.Write(" DEFAULT ");
            writer.Write(
                (column.DefaultValue != null)
                    ? Generate((dynamic)column.DefaultValue)
                    : column.DefaultValueSql
                );
            writer.Write(" FOR ");
            writer.Write(Quote(column.Name));
        }

        Statement(writer);
    }
}

TL;DR / Summary

Here’s the custom migration sql generator:

public class LrpEntitiesMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    private int _dropConstraintCount = 0;

    override protected void Generate(ColumnModel column, IndentedTextWriter writer)
    {
        throw new NotSupportedException("Generate(ColumnModel, IndentedTextWriter) is not supported.");
    }

    override protected void WriteCreateTable(CreateTableOperation createTableOperation, IndentedTextWriter writer)
    {
        if (createTableOperation == null)
        {
            throw new ArgumentNullException(nameof(createTableOperation));
        }
        if (writer == null)
        {
            throw new ArgumentNullException(nameof(writer));
        }
        writer.WriteLine("CREATE TABLE " + Name(createTableOperation.Name) + " (");
        writer.Indent++;
        createTableOperation.Columns.ForEach(
            (c, i) =>
            {
                Generate(c, createTableOperation.Name, writer);
                if (i < createTableOperation.Columns.Count - 1)
                {
                    writer.WriteLine(",");
                }
            });
        if (createTableOperation.PrimaryKey != null)
        {
            writer.WriteLine(",");
            writer.Write("CONSTRAINT ");
            writer.Write(Quote(createTableOperation.PrimaryKey.Name));
            writer.Write(" PRIMARY KEY ");
            if (!createTableOperation.PrimaryKey.IsClustered)
            {
                writer.Write("NONCLUSTERED ");
            }
            writer.Write("(");
            writer.Write(string.Join(", ", createTableOperation.PrimaryKey.Columns.Select(Quote)));
            writer.WriteLine(")");
        }
        else
        {
            writer.WriteLine();
        }
        writer.Indent--;
        writer.Write(")");
    }

    override protected void Generate(AddColumnOperation addColumnOperation)
    {
        if (addColumnOperation == null)
        {
            throw new ArgumentNullException(nameof(addColumnOperation));
        }
        using (IndentedTextWriter writer = Writer())
        {
            writer.Write("ALTER TABLE ");
            writer.Write(Name(addColumnOperation.Table));
            writer.Write(" ADD ");
            ColumnModel column = addColumnOperation.Column;
            Generate(column, addColumnOperation.Table, writer);
            if ((column.IsNullable != null)
                && !column.IsNullable.Value
                && (column.DefaultValue == null)
                && (string.IsNullOrWhiteSpace(column.DefaultValueSql))
                && !column.IsIdentity
                && !column.IsTimestamp
                && !(column.StoreType?.Equals("rowversion", StringComparison.OrdinalIgnoreCase) ?? false)
                && !(column.StoreType?.Equals("timestamp", StringComparison.OrdinalIgnoreCase) ?? false))
            {
                writer.Write(" DEFAULT ");
                if (column.Type == PrimitiveTypeKind.DateTime)
                {
                    writer.Write(Generate(DateTime.Parse("1900-01-01 00:00:00", CultureInfo.InvariantCulture)));
                }
                else
                {
                    writer.Write(Generate((dynamic)column.ClrDefaultValue));
                }
            }
            Statement(writer);
        }
    }

    override protected void Generate(AlterColumnOperation alterColumnOperation)
    {
        SetAnnotatedColumn(alterColumnOperation.Column, alterColumnOperation.Table);
        ColumnModel column = alterColumnOperation.Column;
        using (IndentedTextWriter writer = Writer())
        {
            DropDefaultConstraint(alterColumnOperation.Table, column.Name, writer);
            writer.Write("ALTER TABLE ");
            writer.Write(Name(alterColumnOperation.Table));
            writer.Write(" ALTER COLUMN ");
            writer.Write(Quote(column.Name));
            writer.Write(" ");
            writer.Write(BuildColumnType(column));
            if ((column.IsNullable != null)
                && !column.IsNullable.Value)
            {
                writer.Write(" NOT");
            }
            writer.Write(" NULL");
            if ((column.DefaultValue != null)
                || !string.IsNullOrWhiteSpace(column.DefaultValueSql))
            {
                writer.WriteLine();
                writer.Write("ALTER TABLE ");
                writer.Write(Name(alterColumnOperation.Table));
                writer.Write(" ADD CONSTRAINT ");
                writer.Write(Quote(GetDefaultConstraintName(column.Name, alterColumnOperation.Table)));
                writer.Write(" DEFAULT ");
                writer.Write(
                    (column.DefaultValue != null)
                        ? Generate((dynamic)column.DefaultValue)
                        : column.DefaultValueSql
                    );
                writer.Write(" FOR ");
                writer.Write(Quote(column.Name));
            }
            Statement(writer);
        }
    }

    protected void Generate(ColumnModel column, string tableName, IndentedTextWriter writer)
    {
        if (column == null)
        {
            throw new ArgumentNullException(nameof(column));
        }
        if (writer == null)
        {
            throw new ArgumentNullException(nameof(writer));
        }
        SetAnnotatedColumn(column, tableName);
        writer.Write(Quote(column.Name));
        writer.Write(" ");
        writer.Write(BuildColumnType(column));
        if (column.IsNullable != null
            && !column.IsNullable.Value)
        {
            writer.Write(" NOT NULL");
        }
        if (column.DefaultValue != null)
        {
            writer.Write($" CONSTRAINT {GetDefaultConstraintName(column.Name, tableName)} DEFAULT ");
            writer.Write(Generate((dynamic)column.DefaultValue));
        }
        else if (!string.IsNullOrWhiteSpace(column.DefaultValueSql))
        {
            writer.Write($" CONSTRAINT {GetDefaultConstraintName(column.Name, tableName)} DEFAULT ");
            writer.Write(column.DefaultValueSql);
        }
        else if (column.IsIdentity)
        {
            if (column.Type == PrimitiveTypeKind.Guid
                && (column.DefaultValue == null))
            {
                writer.Write(" DEFAULT " + GuidColumnDefault);
            }
            else
            {
                writer.Write(" IDENTITY");
            }
        }
    }

    private void SetAnnotatedColumn(ColumnModel column, string tableName)
    {
        if (column.Annotations.TryGetValue("SqlDefaultValue", out AnnotationValues values))
        {
            if (values.NewValue == null)
            {
                column.DefaultValueSql = null;
                using (IndentedTextWriter writer = Writer())
                {
                    // Drop Constraint
                    writer.WriteLine(GetSqlDropConstraintQuery(tableName, column.Name));
                    Statement(writer);
                }
            }
            else
            {
                column.DefaultValueSql = (string)values.NewValue;
            }
        }
    }

    private string GetDefaultConstraintName(string columnName, string tableName)
    {
        tableName = tableName.RemoveAllOccurencesOf("dbo.");
        return $"DF_{tableName}_{columnName}";
    }

    private string GetSqlDropConstraintQuery(string tableName, string columnName)
    {
        string[] tableNameSplitByDot = tableName.Split('.');
        string tableSchema = tableNameSplitByDot[0];
        string tablePureName = tableNameSplitByDot[1];
        string str = $@"DECLARE @var{_dropConstraintCount} nvarchar(128)
SELECT @var{_dropConstraintCount} = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'{tableSchema}.[{tablePureName}]')
AND col_name(parent_object_id, parent_column_id) = '{columnName}';
IF @var{_dropConstraintCount} IS NOT NULL
EXECUTE('ALTER TABLE {tableSchema}.[{tablePureName}] DROP CONSTRAINT [' + @var{_dropConstraintCount} + ']')";
        _dropConstraintCount++;
        return str;
    }
}

You will need to use this in combination with the many various solutions for setting the DefaultValueSql property. Personally, I have gone with this solution from StackOverflow and tweaked it to work with the overrides for fixing the default constraint name. The SetAnnotatedColumn(ColumnModel, string) and GetSqlDropConstraintQuery(string, string) methods come from that solution, and have just been slightly tweaked for my environment.

Here’s the summary of the rest of the parts, which, again, have been tweaked from the above solution.

SqlDefaultValue attribute class:

/// <summary>
/// Specifies that a property's backing database column has a default value.
/// </summary>
[AttributeUsage(AttributeTargets.Property)]
public class SqlDefaultValueAttribute : Attribute
{
    public string DefaultValue { get; set; }
    
    public SqlDefaultValueAttribute(string defaultValue)
    {
        DefaultValue = defaultValue;
    }
}

Set the SQL generator in the constructor of your migration configuration like so:

public Configuration()
{
    AutomaticMigrationsEnabled = false;
    CodeGenerator = new CustomMigrationCodeGenerator();
    SetSqlGenerator("System.Data.SqlClient", new CustomMigrationSqlGenerator());
}

Update the OnModelCreating(DbModelBuilder method in your database context with the AttributeToColumnAnnotationConvention convention:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Add(
        new AttributeToColumnAnnotationConvention<SqlDefaultValueAttribute, string>("SqlDefaultValue",
            (p, attributes) => attributes.Single().DefaultValue));
}

And finally, use the attribute on a property to specify that the backing column has a default value:

[SqlDefaultValue("0")]
public bool Example { get; set; }