实体框架不包括插入查询中具有默认值的列 [英] 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")

当我使用<$ c $在数据库中保存新实体时c> 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< bool>（nullable：false，defaultValueSql： 1）

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

中却以1结尾和存储库，所以我有

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 而不进行设置时任何值，insert语句都是：

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 并分配所有值，但是，为了好玩，我将默认值用于非空类型：

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的评论所述，您可以阻止EF为您设置默认值通过添加 ValueGeneratedNever（），例如：

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

现在，值将按原样保存，并且在插入和更新后EF不会回读。总而言之，我认为为非空属性定义默认值没有用。

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屋！