Entity Framework not including columns with default value in insert into query

Question

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

Answer 1

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.

---

Tested with EF Core 1.1.0 preview.

Answer 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.