Default values doesn't set in SQL Server table when creating by Entity Framework mode

Question

I have lots of tables that contain default values, such as CreatedDateTime (getutcdate()). But right now, the value 0001-01-01 00:00:00.0000000 gets stored instead.

https://stackoverflow.com/a/35093135/7731479 --> that is not effective, I have to do it for each table manually for every database model update (edmx). How can I update all StoreGeneratedPattern to Computed automatically? Or why it does not takes computed automatically?

https://stackoverflow.com/a/43400053/7731479 --> ado.net generates all properties and I can't generate again CreatedDateTime.

Are there any automatic solution?

I am using Entity Framework and ado.net.

Person person = new Person()
{
    Id = id,
    Name = name,
};
AddToPerson(person);
SaveChanges();

I want to use above. I don't want use the following and assign CreatedDeteTime again because it is assigned in MSSQL with default value getutcdate().

Person person = new Person()
{
    Id = id,
    Name = name,
    CreatedDeteTime = DateTime.UtcNow;
};
AddToPerson(person);
SaveChanges();

Have you read this answer? https://stackoverflow.com/a/20141145/3563910 — Gonzo345, May 24 '19 at 08:41
Use the modelbuilder fluent api where you define your database context — Matt Luccas Phaure Minet, May 24 '19 at 09:05

score 2 · Answer 1 · answered May 24 '19 at 09:08

The configured default constraint of the SQL Server table will only be applied if you have a SQL INSERT statement that omits the column in question.

So if you insert

 INSERT INTO dbo.Person(Id, Name) VALUES (42, "John Doe")

--> then your CreatedDateTime will automatically be set to the GETUTCDATE() value.

Unfortunately, if you have mapped this column in your EF model class, then this is not what happens. If you create an instance of Person in your C# code, and the CreatedDateTime column is in fact part of the model class, then EF will use something like this to insert the new person:

 INSERT INTO dbo.Person(Id, Name, CreatedDateTime) VALUES (42, "John Doe", NULL)

and since now NULL is in fact provided for the CreatedDateTime column, that's the value that will be stored - or maybe it's an empty string - no matter what, the column is specified in the INSERT statement and thus the configured default constraint is not applied.

So if you want to let SQL Server kick in with the defaults, you need to make sure not to provide the column(s) in question in the INSERT statement at all. You can do this by:

having a separate model class just for inserts, which does not include those columns in question - e.g. have a NewPerson entity, that also maps to the Person table, but only consists of Name and ID for instance. Since those properties aren't there, EF cannot and will not generate an INSERT statement with them - so then the SQL Server default constraints will kick in
map the INSERT method to a SQL Server stored procedure and handle the inserting inside that procedure, by explicitly not specifying those columns you want to have take on default values

CreatedDateTime is not nullable, you can not assign it null. Despite of it has default value as getutcdate() it takes 0001-01-01 00:00:00.0000000 because its StoreGeneratedPattern is not Computed. — Leo, May 24 '19 at 09:49

dkolodin · Answer 2 · 2019-05-24T09:27:50.683

0

May be I'm wrong, but I have a question. If you need to save a default date in your DB Table, why you're trying to save another date from programm level? I mean, it's easy to create a procedure and on the procedure level save the date. Something like (select getdate()...).

edited May 24 '19 at 09:27

answered May 24 '19 at 09:13

dkolodin

23
7

score 0 · Accepted Answer · answered Aug 20 '19 at 16:12

I have found two solutions:

1- This solution solve for all entities that has same property such as CreatedDateTime

public partial class MyEntities : ObjectContext
    {
        public override int SaveChanges(SaveOptions options)
        {
            this.DetectChanges();

            foreach (var insert in this.ObjectStateManager.GetObjectStateEntries(System.Data.EntityState.Added))
            {
                if (insert.Entity.GetType().GetProperty("CreatedDateTime") != null && insert.Entity.GetType().GetProperty("CreatedDateTime").GetType().Name == "DateTime" && (DateTime)(insert.Entity.GetType().GetProperty("CreatedDateTime").GetValue(insert.Entity)) == DateTime.Parse("0001-01-01 00:00:00.0000000"))
                    insert.Entity.GetType().GetProperty("CreatedDateTime").SetValue(insert.Entity, DateTime.UtcNow, null);                
            }
            return base.SaveChanges(options);
        }
    }

referance: https://stackoverflow.com/a/5965743/7731479

2-

public partial class Person
        {
            public Person()
            {
                this.CreatedDateTime = DateTime.UtcNow;
            }
        }

referance : DB default value ignored when creating Entity Framework model

Default values doesn't set in SQL Server table when creating by Entity Framework mode

3 Answers3

Linked