How to generate default sql value in entity framework code first migration file

Question

We have defined a base class as

public class BaseSchema
{
    [Key]
    [ScaffoldColumn(false)]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    
    public bool IsDeleted { get; set; }
    public DateTime PerformedOn { get; set; }
}

Deriving all other classed form this Base schema. Sample class is

public class HoodAudit : BaseSchema{}

After adding HoodAudit in datacontext class and runing migration, migration file generated as

CreateTable(
                "dbo.HoodAudits",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                       //Other properties
                        IsDeleted = c.Boolean(nullable: false),
                        PerformedOn = c.DateTime(nullable: false),
                    },

We want to make PerformedOn as PerformedOn = c.DateTime(nullable: false, defaultValueSql: "GETDATE()"), and IsDeleted as IsDeleted = c.Boolean(nullable: false, defaultValueSql: "0"),

Is there any way we can achieve this? As we are deriving all other classes from BaseSchema, would be great if you suggest any generic way which will apply in al its derived classes. Thank You!

Answer 1

As far as I know, there is no attribute to specify a default database value (SQL calculated or otherwise) on your entity property, instead you need to add the information to your "OnModelCreating" method of your DbContext class and it'll use that info for the migration. You'll find there are many powerful things you can do OnModelCreating that you can't do with attributes...

Here are two sets of examples, one for EF 6, one for EF Core.

Single entity property configuration in EF 6

public class MyDbContext : Microsoft.EntityFrameworkCore.DbContext
{
    protected override void OnModelCreating(DbModelBuilder builder)
    {
        base.OnModelCreating(builder);

        modelBuilder.Entity<MyInheritedEntityType>()
            .Property(p => p.PerformedOn)
            .HasColumnAnnotation("SqlDefaultValue", "getDate()");
    }
}

To do this for ALL entities inheriting from BaseSchema in EF 6, there are three steps:

1. Create an attribute to flag the property needing to be configured in your base class:

/// <summary>
/// Simply an empty attribute that specifies the field should be defaulted to GetDate() in DB.
/// </summary>
[System.AttributeUsage(AttributeTargets.Field | AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
class DefaultToNowAttribute: Attribute
{
}

2. Apply the attribute to any DateTime properties (including in base classes) you want to default to GetDate()

class BaseSchema
{
    [Key]
    [ScaffoldColumn(false)]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public bool IsDeleted { get; set; }
    [DefaultToNow]
    public DateTime PerformedOn { get; set; }
}

3. Use the following in OnModelCreating:

modelBuilder.Properties<DateTime>()
    .Where(x => x.CustomAttributes.Any(y => y.AttributeType == typeof(DefaultToNowAttribute)))
    .Configure(x => x.HasColumnAnnotation("SqlDefaultValue", "getDate()"));

Single Entity Example in EF Core 5:

public class MyDbContext : Microsoft.EntityFrameworkCore.DbContext
{
    protected override void OnModelCreating(DbModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.Entity<HoodAudits>(ha =>
            ha.Property(p => p.PerformedOn).HasDefaultValueSql("GETDATE()"));
    }
}

To do this for ALL entities inheriting from BaseSchema in EF Core 5 you can use the following in OnModelCreating:

var contextType = typeof(MyDbContext);
// A really involved reflection query
foreach (var inerhitsFromBaseSchema in
    System.Reflection.Assembly.GetExecutingAssembly().GetTypes()
        .Where(x => typeof(BaseSchema).IsAssignableFrom(x) // Inherits BaseSchema
            && contextType.FindMembers( // And is a DbSet<> in the context
                System.Reflection.MemberTypes.Property, 
                System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance, 
                (y, z) =>
                    ((System.Reflection.PropertyInfo)y).GetGetMethod().ReturnType.IsGenericType
                    && ((System.Reflection.PropertyInfo)y).GetGetMethod().ReturnType.GetGenericTypeDefinition() == typeof(DbSet<>)
                    && ((System.Reflection.PropertyInfo)y).GetGetMethod().ReturnType.GetGenericArguments()[0] == x,
                null).Length > 0)) // End And is a DbSet<> in the context
    // To run one line against each found object.
    builder.Entity(inheritsFromBaseSchema).Property("PerformedOn")
        .HasDefaultValueSql("GETDATE()");