Bits of .NET

When working with Code First Migrations, developers often struggle with setting default values at the database level. While you can set a default value in your C# class, doing it directly in the SQL schema is much more robust. Here is how you can achieve this using defaultValue and defaultValueSql.

1. Using Static Default Values

For simple types like Booleans or Integers, you can use the defaultValue parameter inside your migration file. This tells SQL Server to assign this value automatically when a new row is inserted.

public override void Up()
{
    CreateTable(
        "dbo.SimpleEntities",
        c => new
            {
                id = c.Long(nullable: false, identity: true),
                name = c.String(),
                deleted = c.Boolean(nullable: false, defaultValue: true), // Set default to true
            })
        .PrimaryKey(t => t.id);
}

After running update-database -verbose, you’ll see that EF generates a SQL query containing the DEFAULT constraint. The resulting table definition will look like this:

CREATE TABLE [dbo].[SimpleEntities] (
    [id]      BIGINT         IDENTITY (1, 1) NOT NULL,
    [name]    NVARCHAR (MAX) NULL,
    [deleted] BIT            DEFAULT ((1)) NOT NULL, -- Database level default
    CONSTRAINT [PK_dbo.SimpleEntities] PRIMARY KEY CLUSTERED ([id] ASC)
);

2. Using Dynamic SQL Values (e.g., GETDATE)

If you need a dynamic value like the current timestamp, defaultValue won’t work because it’s a C# parameter. Instead, you must use defaultValueSql to pass a raw SQL function.

public override void Up()
{
    CreateTable("dbo.Users",
        c => new
            {
                ID = c.Int(nullable: false, identity: true),
                Created = c.DateTime(nullable: false, defaultValueSql: "GETDATE()"),
            })
        .PrimaryKey(t => t.ID);
}

💡 Why use defaultValueSql?

It ensures that the time is generated by the Database Server, not the Application Server. This prevents issues with different time zones or unsynchronized clocks between your web server and database.