为什么我不能保存使用实体框架当前DateTime.Now [英] why i can't save the current DateTime.Now using Entity Framework
问题描述
使用(VAR交易=新的TransactionScope())
{
使用(VAR DB =新MyEntities())
{
VAR新闻组=新组
{
GroupDate = DateTime.Now,
组名=someName
};
db.Groups.Add(新闻组);
db.SaveChanges();
}
transaction.Complete();
}
groupId和GroupDate是PK,GroupId的是标识(步= 1),GroupDate是不是
可以用一个简单的代码时,像这样的,以及如何在关掉开放式并发更新任何一个可以告诉我为什么这个例外发生这是可能的。
商店更新,插入或删除语句影响的行意想不到的
号(0)。实体可能已被修改或自
实体加载删除。刷新ObjectStateManager条目。
块引用>
解决方案这是最有可能的不同精度的问题。 NET
$ b $。 b的DateTime
键入和列键入要使用SQL Server - 大概日期时间
这被发送到数据库
的SaveChanges
INSERT语句是这样的:EXEC sp_executesql的N'insert [DBO]。[返回首页]([GroupDate],[群组])
值(@ 0,@ 1)
选择[GroupId的从[DBO]
[组]
,其中@@ ROWCOUNT过夜。; 0和[的GroupId] = SCOPE_IDENTITY()和[GroupDate] = @ 0',
-N'@ 0 DATETIME2(7),@ 1为nvarchar(50)',
@ 0 ='2013-09 -01 14:21:44.5156250',@ 1 = N'someName'
在.NET
的DateTime
存储小数点后7位数字:0.5156250
。但是,SQL日期时间
键入不能存储这个,因为它具有精度要求不高,有些数字都存储在值后切断。因此,比较[GroupDate] = @ 0
在其中,
子句返回假
和EF获取信息后面什么也没有存储(虽然INSERT实际的具有的被执行),取消交易,并抛出异常。
据我可以看到你只能通过下列变更事项之一解决这个问题:
- 请删除
GroupDate
从主键,即让非键列
- 或更改SQL Server中的列的类型为
DATETIME2(7)
具有相同精度作为.NET的DateTime
键入
或提供您
GroupDate
与精度要求不高,这样的价值完全可以在SQL存储日期时间
键入不带被切断,例如只能用秒精度和毫秒为0
:VAR现在= DateTime.Now;
变种日期=新的日期时间(now.Year,now.Month,now.Day,
now.Hour,now.Minute,now.Second);
VAR新闻组=新组
{
GroupDate =日期,
组名=someName
};
(有可能是从给定的
日期时间删除毫秒更聪明的方法
值比上面的代码,但我无法找到一个合适的了。)
using (var transaction = new TransactionScope()) { using (var db = new MyEntities()) { var newGroup = new Groups { GroupDate = DateTime.Now, GroupName = "someName" }; db.Groups.Add(newGroup); db.SaveChanges(); } transaction.Complete(); }
GroupId and GroupDate is PK, GroupId is Identity(step = 1) and GroupDate is not
can any one tell me why this exception happened when using a simple code like this and how to switch off the Optimistic Concurrency Updates if it's possible
Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.
解决方案It is most likely a problem of the different precisions of the .NET
DateTime
type and the column type you are using in SQL Server - probablydatetime
.The INSERT statement that is sent to the database with
SaveChanges
looks like this:exec sp_executesql N'insert [dbo].[Groups]([GroupDate], [GroupName]) values (@0, @1) select [GroupId] from [dbo].[Groups] where @@ROWCOUNT > 0 and [GroupId] = scope_identity() and [GroupDate] = @0', N'@0 datetime2(7),@1 nvarchar(50)', @0='2013-09-01 14:21:44.5156250',@1=N'someName'
The .NET
DateTime
stores 7 digits after the decimal point:.5156250
. But the SQLdatetime
type cannot store this because it has less precision and some digits are cut off after storing the value. Hence, the comparison[GroupDate] = @0
in thewhere
clause returnsfalse
and EF gets the info back that nothing has been stored (although the INSERT actually has been performed), cancels the transaction and throws the exception.As far as I can see you can solve this problem only by one of the following changes:
- Either remove
GroupDate
from the primary key, i.e. make it a non-key column- Or change the type of the column in SQL Server to
datetime2(7)
which has the same precision as the .NETDateTime
typeOr provide your
GroupDate
with less precision so that the value can be stored completely in a SQLdatetime
type without being cut off, for example only with seconds precision and the milliseconds being0
:var now = DateTime.Now; var date = new DateTime(now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second); var newGroup = new Groups { GroupDate = date, GroupName = "someName" };
(There might be a smarter way to remove the milliseconds from a given
DateTime
value than the code above, but I couldn't find one right now.)这篇关于为什么我不能保存使用实体框架当前DateTime.Now的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!