How can I alter a model to set the SQL default date when using EF code migrations

Question

Firstly, there are a lot of answers in this StackOverflow question that are of a similar ilk which helped me get very close, but none quite work in my situation.

I have a model class:

public class TestModel
{
    [Key]
    public int TestModelId { get; set; }
    public DateTime CreatedOn { get; set; }
}

And this is added to a DbContext file like so:

public virtual DbSet<TestModel> TestModels { get; set; }

So on add-migration this produces:

...
migrationBuilder.CreateTable(
    name: "TestModels",
    columns: table => new 
    {
        TestModelId = table.Column<int>(nullable:false)
            .Annotation("SqlServer:Identity", "1, 1"),
        CreatedOn = table.Column<DateTime>(nullable:false)
    },
    ...

Now I'm trying to get a one-stop place to make sure the CreatedOn column builder has the default GETDATE() in there:

CreatedOn = table.Column<DateTime>(nullable:false, defaultValueSql: "GETDATE()")

which I know works perfectly when translating it to the SQL Server side, but I've tried several data annotation and constructor versions as per the linked question to no avail e.g. the attribute

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]

does nothing and I don't want to have to manually add the defaultValueSql part to the migration file every time I create one / it.

So where can I do this in the models?

While you're at it - get in the habit of using `SYSDATETIME()` instead of `GETDATE()` to get the current date. The old legacy `GETDATE()` only ever returns a `DATETIME` with all its limitations - `SYSDATETIME()` is the more modern, more precise, more flexible version to achieve the same thing that fully supports the "new" date and date/time datatypes we have since SQL Server **2008** ..... — marc_s, Mar 05 '21 at 15:21

score 1 · Accepted Answer · edited Mar 05 '21 at 16:04

1

You can accomplish that in the DbContext class:

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
           modelBuilder.Property(x => x.CreatedOn).HasDefaultValueSql("getdate()");
           .....
        });

edit: needed to have the entity property of the modelBuilder in there too:

modelBuilder.Entity<TestModel>().Property...

edited Mar 05 '21 at 16:04

jamheadart

5,047
4
32
63

answered Mar 05 '21 at 15:26

insane_developer

789
5
6

Ok that was a good hint but missing the reference to the `Entity` itself, I figured it out though, thanks! – jamheadart Mar 05 '21 at 16:03
Oops... that's what happens when you type code in the box itself. Sorry, my bad. – insane_developer Mar 05 '21 at 16:33
ha I actually figured that's what had happened but it was basically spot on answer anyway – jamheadart Mar 05 '21 at 16:57

How can I alter a model to set the SQL default date when using EF code migrations

1 Answers1