DB default value ignored when creating Entity Framework model

Question

Assuming I have the following table in my DB:

CREATE TABLE [dbo].[Test]
(
    [Id]      INT            IDENTITY (1, 1) NOT NULL,
    [Active]  BIT            DEFAULT ((1)) NOT NULL,
)

When creating an EF model from this DB, the mapping for the Active bit column, which is mapped to a Boolean column, has no default value (see property "Default Value):

Why does Entity Framework behave that way? Why doesn't the default value that is defined in the database automatically be applied in the model when the model gets created? The DB states that the default value should be 1, which I assumed would be a default value of true in the model. Do I really have to set the default value for all my columns in the model again?

I checked this behaviour for int and bit columns.

I did some more investigation, and tried to set the "Default Value" property by hand to True and true, and both works.

The auto-generated constructor for the Test-entity changes from

public Test()
{
}

to

public Test()
{
    this.Active = true;
}

So default values would be possible in EF, but they are not set when generating the model based on database-first, at least not on my machine.

So, the question still remains: Do I really have to set the default value for all my columns in the model again, even if they are already set in the DB?

I have to admit that I mixed up two concepts of "default value" when thinking about this. DB default = If a null value is provided for a given column, use the default value. EF default = If a new instance of a certain entity is created, for each property set the default value (e.g. in the constructor). And these two behaviors are conceputally not the same. — Markus Weninger
– Markus Weninger, Commented Oct 29, 2016 at 16:17
But i would expect EF to take DB-Defaults to your created model as a "default" as well. So the developer just has to change, what has to be changed, but not to have a look at thousands of columns with default values... Otherwise why is a property like "not null" read from db? it could be null in EF, if there is a trigger to handle nulls ot something...??? — swe
– swe, Commented Oct 31, 2016 at 9:45
@swe -> Yes, that's also the the way I expected it to work: Creating an instance of some entity (like Test t = new Test()) would be filled with the default values. So ctx.Tests.Add(t); ctx.SaveChanges(); would result in a new entry in the database, containing an Test-entry with Active = 1, because Active "has not been set set manually" (like you said "only change what has to be changed") and therefore should use the default value. But it seems that's not how EF works... — Markus Weninger
– Markus Weninger, Commented Oct 31, 2016 at 11:53
Not sure why EF does this, but because of problems like this I usually maintain at least two other .sql files along with the one the model generates: ExtraSQL.sql and InitialData.sql. "extra sql" are things like these default values; additional SQL rules that need to be applied that aren't easily done through EF. "initial data" is any data that needs to be in the system for it to function. Then I can execute the three scripts and immediately have a working system. — Dave Cousineau
– Dave Cousineau, Commented Apr 17, 2017 at 2:17
@MarkusWeninger, DB default means when you don't use the column in the insert statement that's when DB put's default value, otherwise it will insert the null value if column allows null or will give error. — sallushan
– sallushan, Commented Sep 30, 2017 at 13:07

score 5 · Accepted Answer · 2017-10-01 14:25:42Z

5

Try this:

StoreGeneratedPattern = Calculated

Edit:

Sorry for the one-code-line answer but I really was in a hurry last day.

To make EF set a property’s db default value, the fastest way is to open your .edmx designer, click on the interested field and set

StoreGeneratedPattern = Calculated

In its properties (you can see this property in your second screenshot, where you set “default value” to true).

edited Oct 1, 2017 at 14:25

answered Sep 30, 2017 at 12:39

user8487100

Sign up to request clarification or add additional context in comments.

2 Comments

J...S Over a year ago

Please add more explanation.

CrookedBadge Over a year ago

The "StoreGeneratedPattern" value is "Computed", not "Calculated" I assume that is what you actually meant. The problem with that might be that if you would not like to ALWAYS have default value from database, this in my case worked rather poorly. Since if I assign a value within the code while creating an entity, the value will get overwritten by databases default value.

Leo · Accepted Answer · 2019-08-20 16:14:11Z

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

bryanbcook · Accepted Answer · 2022-02-22 19:31:01Z

0

My workaround is to create a partial class and define a constructor, which sets the variables that have default values automatically. This doesn't solve your initial problem, but at least you don't have to set the default values after every update.

public partial class Test
{
    public Test()
    {
        this.Active = true;
    }
}

edited Feb 22, 2022 at 19:31

bryanbcook

19.7k3 gold badges49 silver badges80 bronze badges

answered Feb 22, 2022 at 17:04

Cratz3r

11 bronze badge

1 Comment

Gert Arnold Over a year ago

They don't have to set the value on every update.

Collectives™ on Stack Overflow

DB default value ignored when creating Entity Framework model

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related