why i can't save the current DateTime.Now using Entity Framework

.net c# entity-framework

Question

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 are both PK; however, GroupId is Identity(step = 1) while GroupDate is not.

Can somebody explain why this exception occurred when using such basic code and, if at all feasible, how to disable the Concurrency Updates with Hope?

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.

1
6
9/1/2013 11:47:08 AM

Accepted Answer

Most likely, the issue is with the.various NET's precisions.DateTime the type and the type of the columns you are utilizing in SQL Server - most likelydatetime .

The database's INSERT statement is submitted withSaveChanges resembles 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'

in the.NETDateTime Following the decimal point, 7 digits are stored:.5156250 However, the SQLdatetime Because type has lower precision and trims certain digits after saving the value, it cannot save this. that's why the comparison[GroupDate] = @0 in thewhere returns Clausefalse and EF receives the information that nothing was saved (despite the fact that the INSERT actually took place at has), cancels the transaction, and raises an exception.

I believe that the only way to resolve this issue is by making one of the following changes:

  • either eliminateGroupDate Make it a non-key column and separate it from the primary key.
  • Alternately, in SQL Server, alter the column's type todatetime2(7) which is as precise to the.NETDateTime type
  • Or offer yourGroupDate less precisely, so that the value can be fully saved in a SQL.datetime without being interrupted, type, for instance, only with seconds precision and 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 may be a more intelligent technique to exclude milliseconds from a given time.)DateTime a better value than the code above, but as of now I couldn't.)

10
9/1/2013 12:38:21 PM

Popular Answer

Avoid turning off the Concurrency Updates with Hope because doing so would only mask the issue. execute the following:

using (var db = new MyEntities())
{    
    using (var transaction = new TransactionScope())
    {
        var newGroup = new Groups
        {
            GroupDate = DateTime.Now,
            GroupName = "someName"
        };
        db.Groups.Add(newGroup);
        db.SaveChanges();
        transaction.Complete();
     }
}

I believe this will solve your issue because you are now constructing the TranscationScope inside the DBContext.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow