why i can't save the current DateTime.Now using 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 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.

Answer 1

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 - probably datetime.

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 SQL datetime 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 the where clause returns false 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 .NET DateTime type

• Or provide your GroupDate with less precision so that the value can be stored completely in a SQL datetime type without being cut off, for example only with seconds precision and the milliseconds being 0:

  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.)