在INSERT期间如何使EF 6处理数据库上的DEFAULT CONSTRAINT [英] How to get EF 6 to handle DEFAULT CONSTRAINT on a database during INSERT
问题描述
问题：
如何让Entity Framework正确处理在执行INSERT时定义了DEFAULT CONSTRAINT的数据库中的列？意思是，如果我在插入操作期间没有在我的模型中提供一个值，那么如何让EF从生成的TSQL INSERT命令中排除该列，以便数据库定义的DEFAULT CONSTRAINT可以工作？
背景
我创建了一个简单的表，只是为了测试实体框架6（EF6）及其与SQL Server能够更新的列的交互。这使用了IDENTITY，TIMESTAMP，COMPUTED以及应用了DEFAULT CONSTRAINT的几列。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo]。[DBUpdateTest]（
[RowID] [int] IDENTITY（200,1）NOT NULL，
[ UserValue] [int] NOT NULL，
[DefValue1] [int] NOT NULL，
[DefValue2null] [int] NULL，
[DefSecond] [int] NOT NULL，
[CalcValue] AS
（（（[rowid] + [uservalue]）+ [defvalue1]）+ [defvalue2null]）* [defsecond]），
[RowTimestamp] [timestamp] NULL，
CONSTRAINT [PK_DBUpdateTest] PRIMARY KEY CLUSTERED
（
[RowID] ASC
）
WITH
（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，
ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）
）
GO
ALTER TABLE [dbo]。[DBUpdateTest]
ADD CONSTRAINT [DF_DBUpdateTest_DefValue1]
DEFAULT（（ 200））FOR [DefValue1]
GO
ALTE R TABLE [dbo]。[DBUpdateTest]
ADD CONSTRAINT [DF_DBUpdateTest_DefValue2null]
DEFAULT（（30））FOR [DefValue2null]
GO
ALTER TABLE [dbo]。[DBUpdateTest]
ADD CONSTRAINT [DF_DBUpdateTest_DefSecond]
DEFAULT（datepart（second，getdate（）））FOR [DefSecond]
GO
EF6完美地处理IDENTITY，TIMESTAMP和COMPUTED列，意思是在INSERT或UPDATE之后（通过 context.SaveChanges（）） EF将新值重新读回到实体对象中以供立即使用。
但是，对于具有DEFAULT CONSTRAINT的列，并不会发生这种情况。而且从我可以看出，这是因为当EF生成TSQL以执行INSERT时，它为可空或不可空类型提供通用默认值，就像该列没有定义DEFAULT CONSTRAINT一样。所以很明显，EF完全忽略了DEFAULT CONSTRAINT的可能性。
这是我的EF代码来插入一个DBUpdateTest记录（我只更新一个列）：
DBUpdateTest myVal = new DBUpdateTest（）;
myVal.UserValue = RND.Next（20，90）;
DB.DBUpdateTests.Add（myVal）;
DB.SaveChanges（）;
这是在INSERT到DBUpdateTest（它尽可能地更新所有可能的列）期间的EF生成的SQL： / p>
exec sp_executesql
N'INSERT [dbo]。[DBUpdateTest]（[UserValue]，[DefValue1]，[DefValue2null ]
[DefSecond]）
VALUES（@ 0，@ 1，NULL，@ 2）
SELECT [RowID]，[CalcValue]，[RowTimestamp]
FROM [dbo ]。[DBUpdateTest]
WHERE @@ ROWCOUNT> 0 AND [RowID] = scope_identity（）'，
N'@ 0 int，@ 1 int，@ 2 int'，@ 0 = 86，@ 1 = 0，@ 2 = 54
请注意，它非常清楚地提供了一个 INT NOT NULL 的默认值）和一个 INT NULL （null），这完全克服了DEFAULT CONSTRAINT。
这是在执行EF INSERT命令时会发生什么，其中它为NULL列提供一个NULL，INT列为零。
RowID UserValue DefValue1 DefValue2null DefSecond CalcValue
= ================================================== ======================
211 100 200 NULL 0 NULL
另一方面，如果我执行此语句：
插入DBUpdateTest （UserValue）值（100）
我会得到这样的记录
RowID UserValue DefV alue1 DefValue2null DefSecond CalcValue
====================================== =============================
211 100 200 30 7 3787
由于一个原因，TSQL INSERT命令没有提供任何具有定义的DEFAULT CONSTRAINT的列的值。
因此，我想要做的是让EF从INSERT TSQL中排除DEFAULT CONSTRAINT列，如果我不明确地在模型对象中设置它们的值。
我已经尝试过的东西
1。识别默认约束？ SO：如何让EF处理默认约束
在 OnModelCreating（）方法我的 DbContext 类，建议我可以告诉EF，具有DEFAULT CONSTRAINT的列是COMPUTED字段，它不是。但是，我想看看是否会使EF至少在INSERT之后读取这个值（不要说它也有可能使我无法为该列分配一个值，这只是我所做的更多不想要）：
modelBuilder.Entity< DBUpdateTest>（）
.Property（e => e.DefValue1 ）
.HasDatabaseGeneratedOption（DatabaseGeneratedOption.Computed）;
这不行，实际上似乎什么也没有什么不同（ED：实际上它的工作，见点2 ）。 EF仍然生成相同的TSQL，提供列的默认值，并在进程中击败数据库。
有没有一个标志，我忘记了一个配置项设置一个可以使用的函数属性，我可以创建一些继承的类代码，以获得EF正确处理DEFAULT CONSTRAINT列？
2。 Get OnModelCreating（）执行？ SO：OnModelCreating not called
Janesh（以下）告诉我，如果列标有，则将从其生成的TSQL INSERT命令中消除参数，强> DatabaseGeneratedOption.Computed 即可。它只是不适合我，因为显然我使用错误的连接字符串（!!!）。
这是我的App.config，这里是< connectionStrings> 部分，我显示坏和好连接字符串：
code><&的ConnectionStrings GT;
< add name =TEST_EF6Entities_NO_WORKYproviderName =System.Data.EntityClientconnectionString =metadata = res：//*/TCXModel.csdl | res：//*/TCXModel.ssdl | res：// * / TCXModel.msl; provider = System.Data.SqlClient; provider connection string =& quot; data source = ... ConnectStringHere ...; App = EntityFramework& quot; />
< add name =TEST_EF6Entities_IT_WORKSproviderName =System.Data.SqlClientconnectionString =data source = ... ConnectStringHere ...; App = EntityFramework; />
< / connectionStrings>
区别：使用 ProviderName 的 code> System.Data.SqlClient ，不工作的使用 System.Data.EntityClient 即可。显然，SqlClient提供程序允许调用 OnModelCreating（）方法，这允许我使用 DatabaseGeneratedOption.Computed 有效果。
==========不要解决========= =
DEFAULT CONSTRAINTS在列上的目的是允许我提供（或不提供）一个值，并且最终得到一个有效的值数据库端。我不必知道SQL Server是这样做的，也不必知道默认值是或应该是什么。这完全超出了我的控制或知识。
关键是，我有一个不提供价值的选项。我可以提供它，或者我无法提供它，如果需要，我可以为每个INSERT做不同的。
使用 DatabaseGeneratedOption.Computed 对于这种情况真的不是一个有效的选项，因为它强制选择：你可以始终提供一个值（因此不要使用数据库默认机制），或者你永远不能提供一个值始终使用数据库默认机制）。
此外，该选项显然仅用于实际计算列，而不适用于具有DEFAULT CONSTRAINT的列，因为一旦应用，模型属性有效地变为READ-仅用于INSERT和UPDATE的目的 - 因为这是一个真正的计算列可以工作。显然，这阻碍了我选择提供或不提供数据库的价值。
所以，我仍然问：如何让EF正常工作具有定义DEFAULT CONSTRAINT的数据库列？
这个位是您问题的关键：
因此，我想要做的是让EF不要在其INSERT TSQL中包含
DEFAULT CONSTRAINT列，如果我不明确
在对象中设置它们的值。
实体框架不会为您做。始终计算字段或始终包含在插入和更新中。
但是你可以按照你所描述的方式写类。您必须将字段（显式）设置为构造函数中的默认值，或使用后备字段。
public class DBUpdateTest
/ * public partial class DBUpdateTest * / //数据库首先的版本
{
private _DefValue1 = 200;
private _DefValue2 = 30;
public DbUpdateTest（）
{
DefSecond = DateTime.Second;
}
public DefSecond {get;组; }
public DefValue1
{
get {return _DefValue1; }
set {_DefValue1 = value; }
}
public DefValue2
{
get {return _DefValue2; }
set {_DefValue2 = value; }
}
}
如果你总是插入这些类，那么你可能不需要在数据库中设置默认值，但是如果使用其他地方使用sql插入，则必须将默认约束添加到数据库中
I am new to EF (its my first week), but not new to databases or to programming. Others have asked similar questions, but I don't feel that it has been asked with the right detail or explained quite as it needs to be explained, so here I go.
Question: How do I get Entity Framework to properly deal with columns in a database that have a DEFAULT CONSTRAINT defined when performing an INSERT? Meaning, if I do not supply a value in my model during an insert operation, how do I get EF to exclude that column from its generated TSQL INSERT command, so that the database-defined DEFAULT CONSTRAINT will work?
Background
I have a simple table I created, just to test Entity Framework 6 (EF6) and its interaction with the columns SQL Server is capable of updating. This utilizes IDENTITY, TIMESTAMP, COMPUTED, and a few columns with a DEFAULT CONSTRAINT applied.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DBUpdateTest](
[RowID] [int] IDENTITY(200,1) NOT NULL,
[UserValue] [int] NOT NULL,
[DefValue1] [int] NOT NULL,
[DefValue2null] [int] NULL,
[DefSecond] [int] NOT NULL,
[CalcValue] AS
(((([rowid]+[uservalue])+[defvalue1])+[defvalue2null])*[defsecond]),
[RowTimestamp] [timestamp] NULL,
CONSTRAINT [PK_DBUpdateTest] PRIMARY KEY CLUSTERED
(
[RowID] ASC
)
WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
ALTER TABLE [dbo].[DBUpdateTest]
ADD CONSTRAINT [DF_DBUpdateTest_DefValue1]
DEFAULT ((200)) FOR [DefValue1]
GO
ALTER TABLE [dbo].[DBUpdateTest]
ADD CONSTRAINT [DF_DBUpdateTest_DefValue2null]
DEFAULT ((30)) FOR [DefValue2null]
GO
ALTER TABLE [dbo].[DBUpdateTest]
ADD CONSTRAINT [DF_DBUpdateTest_DefSecond]
DEFAULT (datepart(second,getdate())) FOR [DefSecond]
GO
EF6 handles the IDENTITY, TIMESTAMP, and COMPUTED columns perfectly, meaning after INSERT or UPDATE (via context.SaveChanges()) EF reads the new values back into the entity object for immediate use.
However, this does not happen for the columns with the DEFAULT CONSTRAINT. And from what I can tell, this is because when EF generates the TSQL to perform the INSERT, it supplies the common default value for nullable or non-nullable types, just as if that column had no DEFAULT CONSTRAINT defined on it. So it seems clear that EF completely ignores the possibility of a DEFAULT CONSTRAINT.
Here is my EF code to INSERT a DBUpdateTest record (and I only update a single column):
DBUpdateTest myVal = new DBUpdateTest();
myVal.UserValue = RND.Next(20, 90);
DB.DBUpdateTests.Add(myVal);
DB.SaveChanges();
Here is the EF generated SQL during an INSERT to DBUpdateTest (which dutifully updates all possible columns):
exec sp_executesql
N'INSERT [dbo].[DBUpdateTest]([UserValue], [DefValue1], [DefValue2null],
[DefSecond])
VALUES (@0, @1, NULL, @2)
SELECT [RowID], [CalcValue], [RowTimestamp]
FROM [dbo].[DBUpdateTest]
WHERE @@ROWCOUNT > 0 AND [RowID] = scope_identity()',
N'@0 int,@1 int,@2 int',@0=86,@1=0,@2=54
Note that it is very clearly supplying what would be the default value for an INT NOT NULL (0) and an INT NULL (null), which completely overcomes the DEFAULT CONSTRAINT.
This is what happens when the EF INSERT command executes, where it supplies a NULL for the nullable column and a ZERO for the INT column
RowID UserValue DefValue1 DefValue2null DefSecond CalcValue
=========================================================================
211 100 200 NULL 0 NULL
If, on the other hand, I execute this statement:
insert into DBUpdateTest (UserValue) values (100)
I will get a record like so
RowID UserValue DefValue1 DefValue2null DefSecond CalcValue
=========================================================================
211 100 200 30 7 3787
This works as expected for one reason: the TSQL INSERT command did not provide values for any columns with a defined DEFAULT CONSTRAINT.
What I am trying to do, therefore, is to get EF to exclude the DEFAULT CONSTRAINT columns from the INSERT TSQL if I do not explcitly set values for them in the model object.
Things I Already Tried
1. Recognize Default Constraint? SO: How to get EF to handle a Default Constraint
In the OnModelCreating() method of my DbContext class, it was recommended that I could tell EF that a column with the DEFAULT CONSTRAINT is a COMPUTED field, which it is not. However, I wanted to see if it would get EF to at least read the value back after the INSERT (never mind that it would also likely keep me from being able to assign a value to that column, which is just more of what I do not want):
modelBuilder.Entity<DBUpdateTest>()
.Property(e => e.DefValue1)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);
This does not work, and in fact appears to do nothing different at all (ED: actually it does work, see point 2). EF still generates the same TSQL, providing defaults for the columns and defeating the database in the process.
Is there a flag I am missing, a configuration item I am forgetting to set, a function attribute I can use, some inherited class code I can create, to get EF to "handle DEFAULT CONSTRAINT columns correctly?"
2. Get OnModelCreating() to execute? SO: OnModelCreating not called
Janesh (below) showed me that EF will eliminate parameters from its generated TSQL INSERT command if the column is marked with DatabaseGeneratedOption.Computed. It just wasn't working for me because apparently I was using the wrong kind of connect string (!!!).
Here's my App.config, and here's the <connectionStrings> section where I show the "bad" and "good" connect string:
<connectionStrings>
<add name="TEST_EF6Entities_NO_WORKY" providerName="System.Data.EntityClient" connectionString="metadata=res://*/TCXModel.csdl|res://*/TCXModel.ssdl|res://*/TCXModel.msl;provider=System.Data.SqlClient;provider connection string="data source=...ConnectStringHere...;App=EntityFramework"" />
<add name="TEST_EF6Entities_IT_WORKS" providerName="System.Data.SqlClient" connectionString="data source=...ConnectStringHere...;App=EntityFramework;" />
</connectionStrings>
The difference: The one that works uses ProviderName of System.Data.SqlClient, the one that does not work uses System.Data.EntityClient. Apparently the SqlClient provider allows the OnModelCreating() method to be called, which allows my use of DatabaseGeneratedOption.Computed to have an effect.
========== NOT YET SOLVED ==========
The purpose of DEFAULT CONSTRAINTS on columns is to allow me to supply (or not supply) a value, and still end up with a valid value on the database side. I don't have to know that SQL Server is doing this, nor do I have to know what the default value is or should be. This happens completely outside my control or knowledge.
The point is, I have the option of not supplying the value. I can supply it, or I can fail to supply it, and I can do that differently for each INSERT if needed.
Using DatabaseGeneratedOption.Computed is really not a valid option for this case because it forces a choice: "you can ALWAYS provide a value (and therefore NEVER utilize the database default mechanism), or you can NEVER provide a value (and therefore ALWAYS utilize the database default mechanism)".
Plus, that option is clearly intended to be used only on actual Computed Columns, and not for columns with DEFAULT CONSTRAINTs, because once applied, the model property effectively becomes READ-ONLY for purposes of INSERT and UPDATE - because that's how a real Computed Column would work. Obviously this stands in the way of my choice to supply or not supply a value to the database.
So, I still ask: How can I get EF to work "correctly" with database columns that have a DEFAULT CONSTRAINT defined?
This bit is the key to your question:
What I am trying to do, therefore, is to get EF NOT to include the DEFAULT CONSTRAINT columns in its INSERT TSQL if I do not explcitly set values for them in the object.
Entity Framework won't do that for you. Fields are either always computed or always included in inserts and updates. But you CAN write the classes to behave in the way that you describe. You have to set the fields (explicitly) to the default values in the constructor, or using backing fields.
public class DBUpdateTest
/* public partial class DBUpdateTest*/ //version for database first
{
private _DefValue1 = 200;
private _DefValue2 = 30;
public DbUpdateTest()
{
DefSecond = DateTime.Second;
}
public DefSecond { get; set; }
public DefValue1
{
get { return _DefValue1; }
set { _DefValue1 = value; }
}
public DefValue2
{
get { return _DefValue2; }
set { _DefValue2 = value; }
}
}
If you always insert using these classes, then you probably don't need to set the default in the database, but if you insert using sql from elsewhere, then you will have to add the default constraint to the database too
这篇关于在INSERT期间如何使EF 6处理数据库上的DEFAULT CONSTRAINT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
