实体框架在插入到查询中不包括具有默认值的列 [英] Entity Framework not including columns with default value in insert into query
问题描述
我有一个模型，其中一些列定义了默认值，例如
I have a model that has some columns defined with default values like
table.Column<bool>(nullable: false, defaultValueSql: "1")
当我使用 context.SaveChanges() 在数据库中保存一个新实体时，我注意到具有默认值的列不包含在 Entity Framework 生成的插入查询中，因此值在数据库中生成的是默认的，而不是我在模型中传递的.
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.
我是否必须在上下文中设置一些属性才能通过代码设置这些属性?我正在使用 EF Core，但我不知道这是否是所有 EF 版本的一般行为.
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.
更新:代码非常简单.这是我所拥有的伪代码.模型有一些用约束定义的属性，比如我上面描述的那个table.Column
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")
我将使用列 MyBooleanProperty 作为示例.我有一个服务:
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();
在 VS 的输出窗口中，我看到它如何在 INSERT INTO 查询中发送所有属性，然后对具有默认值的属性执行 SELECT.结果是数据库中的 newModel 包含我发送的所有值，但具有默认值的列除外.我无法更改这些表的配置，因为它正被另一个需要这些规则的系统使用.
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
推荐答案
我认为这是一个错误.
我拼凑了一个简单的测试，只是一个带有一些默认值的类:
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; }
}
(注意 DateTime 可以为空)
(Note that the DateTime is nullable)
映射:
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语句:
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])
);
当我插入一个新的Test而不设置任何值时，插入语句是:
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();
您看到插入后从数据库中读取了三个默认值(以及生成的标识值).[顺便说一下，这是 EF-Core 中的新功能.在 EF6 中，只有在插入(和更新)后从数据库中读取标记为 DatabaseGeneratedOption.Computed 的标识值和列值.
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)].
这是创建的Test对象:
ID IntDef BoolDef DateDef
1 1 True 21-11-16 19:52:56
现在我插入一个新的 Test 并分配所有值，但是，为了好玩，我对不可为 null 的类型使用默认值:
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
};
SQL 语句如下:
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'
当然，EF 无法推断默认值是故意分配的.因此，如您所见，仅为可为空的 DateTime 列插入分配的值，而不为不可为空的列插入.现在 DateDef 的值在插入后不会从数据库中读取.
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.
实体值为:
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!
这意味着:
当您在 EF-Core 中使用默认值配置属性时，并且此默认值与 .Net 默认值不同，您无法插入具有 .Net 类型默认值的实体(类似于布尔值的 false).
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).
我认为这是一个严重的错误，甚至可能会取消有关默认值的新 EF-Core 行为.
I think this is a serious bug, maybe it even disqualifies the new EF-Core behaviour concerning defaults.
添加
正如 Ivan 的评论中所说，您可以通过添加 ValueGeneratedNever() 来阻止 EF 为您设置默认值，例如:
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();
现在该值将按原样保存，EF 不会在插入和更新后读回它.总而言之，我认为为不可为 null 的属性定义默认值没有用.
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.
使用 EF Core 1.1.0 预览版进行测试.
Tested with EF Core 1.1.0 preview.
这篇关于实体框架在插入到查询中不包括具有默认值的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
