21

I have a model that has some columns defined with default values like 

table.Column<bool>(nullable: false, defaultValueSql: "1")

When I save a new entity in the database using context.SaveChanges(), I noticed that the columns with default values are not included in the insert into query that Entity Framework generates, so the values generated in the database are the default ones instead of the ones I'm passing in the model.

Do I have to set up some property in the context to be able to set these properties through code? I'm using EF Core, but I don't know if this is a general behavior of all EF versions.

UPDATE: the code is pretty simple. This is pseudo code of what I have. The Model has some properties defined with constraints such as the one I describe above table.Column<bool>(nullable: false, defaultValueSql: "1")

I'll use column MyBooleanProperty as an example. I have in a service:

var newModel = new GEAddress();
newModel = someEntity.MyBooleanProperty; //it is false,but ends up as 1 in the database

I'm using Unit Of Work and Repositories so I have 

_unitOfWork.MyModelRepository.Add(newModel);
_unitOfWork.SaveChanges();

In the output window of VS, I see how it send all properties in an INSERT INTO query and then it does a SELECT on the properties with default values. The result is the newModel in the database with all the values I sent, except the columns with default values. I cannot change the configuration for those tables since it's being used by another system that needs those rules.

I would like to know an explanation on why this is happening more than a solution. I can work around this, but I'd like to know why this behavior is happening

Improve this question
11
  • General behavior is "The default value of a column is the value that will be inserted if a new row is inserted but no value is specified for the column." In your case looks like you are losing the values that are set. can you post your code? Commented Nov 15, 2016 at 21:25
  • FWIW, EF6 does not support Default Values - you must explicitly specify a value for all columns, even if a non-NULL column has an explicit default-value set. Commented Nov 15, 2016 at 21:45
  • In that case you can remove the configuration for your column to disable default values behavior, in that way the default value will provide by database. Can you show your code (DbContext, Mappings, Pocos) ? Commented Nov 16, 2016 at 7:05
  • Please show how you mapped the property. If it is queried after an insert is must have been mapped as DatabaseGeneratedOption.Computed. Commented Nov 18, 2016 at 14:14
  • Which EF Core version? Commented Nov 19, 2016 at 16:52

4 Answers 4

Reset to default
23
+50

This was finally solved in EF8. See my second answer.

Original answer:

I would call this a bug.

I snapped together a simple test, just a class with some defaults:

public class Test
{
    public int ID { get; set; }
    public int IntDef { get; set; }
    public bool BoolDef { get; set; }
    public DateTime? DateDef { get; set; }
}

(Note that the DateTime is nullable)

The mapping:

modelBuilder.Entity<Test>().HasKey(a => a.ID);
modelBuilder.Entity<Test>().Property(s => s.DateDef).HasDefaultValueSql("GETDATE()");
modelBuilder.Entity<Test>().Property(s => s.IntDef).HasDefaultValueSql("1");
modelBuilder.Entity<Test>().Property(s => s.BoolDef).HasDefaultValue(true);
// Equivalent:
// modelBuilder.Entity<Test>().Property(s => s.BoolDef).HasDefaultValueSql("1");

SQL statement that creates the table:

CREATE TABLE [Tests] (
    [ID] int NOT NULL IDENTITY,
    [BoolDef] bit NOT NULL DEFAULT 1,
    [DateDef] datetime2 DEFAULT (GETDATE()),
    [IntDef] int NOT NULL DEFAULT (1),
    CONSTRAINT [PK_Tests] PRIMARY KEY ([ID])
);

When I insert a new Test without setting any value, the insert statement is:

INSERT INTO [Tests]
DEFAULT VALUES;
SELECT [ID], [BoolDef], [DateDef], [IntDef]
FROM [Tests]
WHERE @@ROWCOUNT = 1 AND [ID] = scope_identity();

You see that the three default values (and the generated identity value) are read from the database after the insert. [By the way, this is new in EF-Core. In EF6, only identity values and column values that were marked as DatabaseGeneratedOption.Computed were read from the database after insert (and update)].

This is the created Test object:

ID IntDef BoolDef DateDef
1  1      True    21-11-16 19:52:56

Now I insert a new Test and assign all values, but, just for fun, I use the default values for the non-nullable types:

var item = new Test
{ 
    IntDef = default(int), // 0 
    BoolDef = default(bool), // false
    DateDef = default(DateTime), // 01-01-01 0:00:00
};

Here's the SQL statement:

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Tests] ([DateDef])
VALUES (@p0);
SELECT [ID], [BoolDef], [IntDef]
FROM [Tests]
WHERE @@ROWCOUNT = 1 AND [ID] = scope_identity();
',N'@p0 datetime2(7)',@p0='0001-01-01 00:00:00'

Of course, EF has no way to infer that the default values were assigned deliberately. So as you see, only for the nullable DateTime column the assigned value is inserted, not for the non-nullable columns. Now the value for DateDef isn't read from the database after the insert.

The entity values are:

ID IntDef BoolDef DateDef
1  1      True    01-01-01 0:00:00

Not what one would expect after saving the entity --not at all!

Which means:

When you configure a property with a default value in EF-Core, and this default is different than the .Net default value, you can't insert an entity with default values for the .Net type (like false for a boolean).

I think this is a serious bug, maybe it even disqualifies the new EF-Core behaviour concerning defaults.

Addition
As said in Ivan's comment, you can stop EF from setting default values for you by adding ValueGeneratedNever(), for example:

modelBuilder.Entity<Test>().Property(s => s.IntDef)
            .HasDefaultValueSql("1").ValueGeneratedNever();

Now the value will be saved as it is and EF won't read it back after inserts and updates. All in all, I think defining defaults for non-nullable properties isn't useful.

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

8 Comments

I'll mark this as answer since it confirms that indeed there's something wrong with that behavior. thanks!
Just to add that currently there is no way to control that behavior dynamically at runtime. All you can do is to turn it off statically using ValueGeneratedNever().
Thanks @Ivan, didn't know this one yet. I think we can prepare for tons of questions coming in at SO concerning this behavior.
I posted the issue in github and it seems you have to define the properties as nullable so the CLR will know when to assignthe value you're passing. I haven't tested all cases, but here is the link github.com/aspnet/EntityFramework/issues/7089
@CPerkins Yep, although .HasDefaultValueSql(...).ValueGeneratedNever() is a reasonable work-around.
|
6

This annoying bug has finally been solved, not earlier than in EF8. For details, see the What's new announcement.

A brief demonstration of the idea probably works better.

Take this class:

class Test
{
    public int Id { get; set; }
    public bool NotNullDefaultFalse { get; set; }
    public bool NotNullDefaultTrue { get; set; }
    public bool? NullDefaultFalse { get; set; }
    public bool? NullDefaultTrue { get; set; }
    public string Description { get; set; }
}

It's got a number of bool properties, nullable and not-nullable and with defaults false or true. Until EF7 we had this API to configure database defaults:

protected override void OnModelCreating(ModelBuilder mb)
{
    var etb = mb.Entity<Test>();

    etb.Property(e => e.NotNullDefaultFalse).HasDefaultValue(false);
    etb.Property(e => e.NotNullDefaultTrue).HasDefaultValue(true);
    etb.Property(e => e.NullDefaultFalse).HasDefaultValue(false);
    etb.Property(e => e.NullDefaultTrue).HasDefaultValue(true);
}

Inserting a couple of items:

new[]
{
    new Test { Description = "ClrDefaults" },
    new Test 
    { 
        NotNullDefaultFalse = false,
        NotNullDefaultTrue = true,
        NullDefaultFalse = false,
        NullDefaultTrue = true,
        Description = "Defaults set - same"
    },
    new Test
    {
        NotNullDefaultFalse = true,
        NotNullDefaultTrue = false,
        NullDefaultFalse = true,
        NullDefaultTrue = false,
        Description = "Defaults set - opposite"
    },
};

To recap the problem, see the results after saving in EF7 (bold values are wrong):

Id NotNullDefaultFalse NotNullDefaultTrue NullDefaultFalse NullDefaultTrue Description
1 False True False True ClrDefaults
2 False True False True Defaults set - same
3 True True True False Defaults set - opposite

The errors have to do with EF not knowing when a property has been set explicitly; it gets the database default value too often.

EF8

EF8 has added a method to add a sentinel value to a default:

Configures the value that will be used to determine if the property has been set or not. If the property is set to the sentinel value, then it is considered not set.

In other words, you tell EF "if my property has any value other than this sentinel value, then consider it set by me and save it. Otherwise, let the database default set the value."

According to EF...

By default, the sentinel value is the CLR default value for the type of the property.

However, in my experience, for nullable properties you have to set the sentinel value explicitly. Which leads to this minor change in the configuration. Notice these HasSentinel calls.

// In not-null properties, sentinels are set implicitly in HasDefaultValue.
etb.Property(e => e.NotNullDefaultFalse).HasDefaultValue(false);
etb.Property(e => e.NotNullDefaultTrue).HasDefaultValue(true);
// In nullable properties, sentinels must be set explicitly!
etb.Property(e => e.NullDefaultFalse).HasDefaultValue(false).HasSentinel(false);
etb.Property(e => e.NullDefaultTrue).HasDefaultValue(true).HasSentinel(true);

And, finally, the correct results:

Id NotNullDefaultFalse NotNullDefaultTrue NullDefaultFalse NullDefaultTrue Description
1 False False null null ClrDefaults
2 False True False True Defaults set - same
3 True False True False Defaults set - opposite
Improve this answer

Comments

2

If you don't mark the property as computed with the proper attribute 

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]

ef will generate the insert statement with the property value.
During update EF generates the UPDATE statement SET clause inserting only changed values.

You have already a workaround anyway, if the property is only generated by the DBMS you can use the attribute above otherwise you have to insert the default value in the constructor of the class that rapresent the entity.

Improve this answer

3 Comments

DatabaseGeneratedOption.Computed indicates a column whose value will always be provided by the database, causing EF to ignore updates on that column from user code. The OP only wants to provide a default value to be used when a value is not specified, but to be able to specify it upon insertion or modification.
I agree with @bubi, I have other columns with that attribute and they work fine. My issue is the ones with default values that are not getting the inserted valu
@CodeCaster the problem is that the concept not specified for EF does not exist (exists the concept changed for the tracked entities) so if you don't specify Computed EF will always generate the insert statement.
0

ValueGeneratedNever didn't do the job for me (Entity Framework Core 3.1.21). I simply deleted .HasDefaultValueSql from DBContext and leaved default value in database schema. Works fine, maybe this note can be useful for somebody

Improve this answer

1 Comment

I'm using EF Core 6 with code-first migrations. After adding ValueGeneratedNever to the affected columns in DBContext.OnModelCreating(), I updated the migrations. Although the new migration itself was blank--no change to the database, it did update the *DbContextModelSnapshot.cs to match the change to the columns. Tests reveal that this resolved the problem without changing the HasDefaultValueSql calls.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.