Possible to default DateTime field to GETDATE() with Entity Framework Migrations?

Question

I added EntityFramework.Migrations (Beta 1) to an existing Code-First app that is going through some changes (for both migration capabilities and more fine-tuning of the tables I am generating from my code-first API) and ran into the GETDATE() scenario.

I was already using a custom initializer class in my DbContext to run SQL scripts to set some fields and create indexes in my database. A handful of my AlterTable scripts are primary just to setup fields with default values(such as certain DateTime fields being set to GETDATE()). I was really hoping EntityFramework.Migrations would have an answer for this since you can easily specify defaultValue, but so far I'm not seeing one.

Any ideas? I was really hoping that doing the following would magically work. (It is 'magic unicorn', after all)

DateCreated = c.DateTime(nullable: false, defaultValue: DateTime.Now)

Unfortunately, and logically, it set my default value to the time when the Update-Database command was executed.

Inquiring

I'm not looking into getting into the theories revolving around '...by using an ORM, you shouldn't be putting logic into the database...'. I understand that. Just want to make sure nobody throws back a POCO example with DateTime.Now in the constructor :) – Inquiring

just had a similar probably, and found a good solution. Hope this helps: #9830716 – Midtown

Answer 1

You must use custom SQL script in Up method for setting default value:

Sql("ALTER TABLE TableName ADD CONSTRAINT ConstraintName DEFAULT GETDATE() FOR ColumnName");

Setting default value in code allows only static values - no database level functions.

Anyway setting it in POCO constructor is correct way if you are going to use code first. Also if you want to set the value in the application for some special cases you cannot use a default value in the database because the default value in the database requires either DatabaseGeneratedOption.Identity or DatabaseGeneratedOption.Computed. Both these options allow setting the property only in the database.

Edit:

Since the product is still in development my answer is no longer valid. Check @gius answer for actual way to achieve this requirement by using defaultValueSql (it wasn't available in EF Migrations Beta 1 but was added in EF 4.3 Beta 1 which already includes migrations).

Ebby

Thanks. I already had the SQL Script functionality in place prior to moving to EF.Migrations so this doesn't really help me any. – Inquiring

I marked this as the answer. Not the answer I was hoping for, but I figured this to be the case. – Inquiring

Answer 2

You can use

DateCreated = c.DateTime(nullable: false, defaultValueSql: "GETDATE()")

Usage:

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

Update 2012-10-10:

As requested by Thiago in his comment, I add a little extra context.

The code above is a migration-file generated by EF Migrations by running Add-Migration MyMigration as a command in the package manager console. The generated code is based on the models in the DbContext associated with migrations. The answer suggests that you modify the generated script so that a default value is added when the database is created.

You can read more about Entity Framework Code First Migrations here.

Flagrant

where does this code go?? what is the variable "c"?? Can you provide a little more context around this line of code? – Midtown

@ThiagoSilva Added a little extra context to the answer, as you suggested. – Retarded

easiest answer. – Erwinery

Answer 3

I recently ran in to this issue in EF6 (since they still haven't fixed it). The easiest way I found to do it without having to manually modify the Migration class is to override the CodeGenerator in your Configuration class.

By creating a class that implements MigrationCodeGenerator and then overriding the Generate method you can iterate through all of the operations and apply what ever modifications you want.

Once your modifications have been made, you can then initialize your the CSharpMigrationCodeGenerator and return its default value.

public class ExtendedMigrationCodeGenerator : MigrationCodeGenerator
{
    public override ScaffoldedMigration Generate(string migrationId, IEnumerable<MigrationOperation> operations, string sourceModel, string targetModel, string @namespace, string className)
    {
        foreach (MigrationOperation operation in operations)
        {
            if (operation is CreateTableOperation)
            {
                foreach (var column in ((CreateTableOperation)operation).Columns)
                    if (column.ClrType == typeof(DateTime) && column.IsNullable.HasValue && !column.IsNullable.Value && string.IsNullOrEmpty(column.DefaultValueSql))
                        column.DefaultValueSql = "GETDATE()";
            }
            else if (operation is AddColumnOperation)
            {
                ColumnModel column = ((AddColumnOperation)operation).Column;

                if (column.ClrType == typeof(DateTime) && column.IsNullable.HasValue && !column.IsNullable.Value && string.IsNullOrEmpty(column.DefaultValueSql))
                    column.DefaultValueSql = "GETDATE()";
            }
        }

        CSharpMigrationCodeGenerator generator = new CSharpMigrationCodeGenerator();

        return generator.Generate(migrationId, operations, sourceModel, targetModel, @namespace, className);
    }
}

internal sealed class Configuration : DbMigrationsConfiguration<Project.Models.Context.DatabaseContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        MigrationsDirectory = @"Migrations";
        this.CodeGenerator = new ExtendedMigrationCodeGenerator();
    }
}

I hope this helps

Linalool

I wish I could award an ad-hoc rep bounty to you for this. I'm taking an existing project that had started its life as an nHibernate project that someone then changed to EF6. They didn't go the extra mile to get everything on migrations due to time constraints, which I intend to do now. This made it possible to delete several one-off scripts they ran in their hand-rolled DB generator. Thank you! – Poliard

@Linalool Overriding the MigrationCodeGenerator is a great idea. But why do not retrieve an attribute / annotations of the columns (class fields) with the default value ([DefVal("getutcdate()")]) instead of doing so to all the columns. Is it possible? Because I'm trying but I can't... – Recreant

I ended up needing to use SqlServerMigrationSqlGenerator since I use Automatic Migrations but I didn't need to change much – Razz

Answer 4

Create a migration:

public partial class Table_Alter : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.tableName", "columnName", 
           c => c.DateTime(nullable: false, defaultValueSql: "GETDATE()"));
    }

    public override void Down()
    {
        DropColumn("dbo.tableName", "columnName");
    }
}

For existing records it will set the datetime when you will run Update-Database command, for new records it will be set the datetime of creation

Lachrymatory

Answer 5

Alternatively if your entities inherit from a common interface you can override the SaveChanges method on the DbContext and set or update properties at that point (great for Created Date and Last Changed Date)

Alluring

Answer 6

This is the most simple way.

First Add DatabaseGeneratedOption.Computed DataAnnotion to your property

and now you can modify de SqlServerMigrationSqlGenarator, override Genarate method and set the DefaultValueSql = "GETDATE()" or "GETUTCDATE()";

Raquelraquela

This answer should probably include the specific custom SqlServerMigrationSqlGenerator. JonnySchnittger's answer is a broad brush and doesn't include support for per-property attribute annotation or fluent configuration definitions. (If I end up implementing this instead of modifying the migration, I'll edit it) – Turgot

Answer 7

Using Entity Framework with .net 6 I was able to make the migration add the default time by changing the defaultValue to defaultValueSql:

public partial class ReportQueueAddCreatedAt : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<DateTime>(
            name: "CreatedAt",
            table: "ReportQueue",
            type: "datetime2",
            nullable: false,
            defaultValueSql: "GETDATE()");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "CreatedAt",
            table: "ReportQueue");
    }
}

Flann

This is the answer with the least amount of effort to use, thanks. It also works with .net 5 btw. – Leucas

Answer 8

An improvement: check if the constraint exists:

Sql(@"
if not exists (
    select *
      from sys.all_columns c
      join sys.tables t on t.object_id = c.object_id
      join sys.schemas s on s.schema_id = t.schema_id
      join sys.default_constraints d on c.default_object_id = d.object_id
    where 
      d.name = 'DF_ThubOutputEmail_Created'
)
begin
    ALTER TABLE dbo.ThubOutputEmails ADD CONSTRAINT DF_ThubOutputEmail_Created default getdate() for Created;
end");

Tenatenable