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.

Not sure what the problem is in your case, but I don't recall `DateTime.Now` requiring anything special to work with an EF entity. — Theodoros Chatzigiannakis, Sep 01 '13 at 10:40
Never worked with `TransactionScope`, but is it possible that you need to "switch" the two usings so the scope is inside the context, because after the end of the context using it is disposed and the transaction can not access the DB anymore? Because I use `DateTime.Now` a lot in combination with EF and did never have that problem... — Christoph Fink, Sep 01 '13 at 10:54
[chrfin](http://stackoverflow.com/users/472434/chrfin) the same exception — HB MAAM, Sep 01 '13 at 11:06
Does it happen without the transaction? Can you show us the Code of `Groups`? — Christoph Fink, Sep 01 '13 at 11:11
yes it does, groups is a entity in edmx model, each column has None in his concurrency Mode property — HB MAAM, Sep 01 '13 at 11:17
as i said in the question groups is just a table has tow PK groupId and groupDate and groupId is identity step 1 — HB MAAM, Sep 01 '13 at 11:41
Then I have no idea, sorry. Maybe take a look at Bassam's link... — Christoph Fink, Sep 01 '13 at 11:53
if you used DateTime.Now.Date instead of DateTime.Now it will work but i don't know why — HB MAAM, Sep 01 '13 at 12:03
Are you using Oracle? This also fails for me when using the Oracle EF provider but it works using the SQL Server provider — C. Tewalt, May 14 '15 at 22:38

score 10 · Accepted Answer · answered Sep 01 '13 at 12:38

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

I think the better code removing the `milliseconds` part you mentioned may be this: `var date = now.AddMilliseconds(-now.Millisecond);` — King King, Sep 02 '13 at 00:07

score 1 · Answer 2 · edited Sep 01 '13 at 11:39

1

Do not switch the Optmistic Concurrency Updates off you will hide the problem and not fix it. do 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();
     }
}

The different now you are creating the TranscationScope inside the DBContext I hope this will solve your problem.

edited Sep 01 '13 at 11:39

user2674389

1,123
7
8

answered Sep 01 '13 at 11:16

Bassam Alugili

16,345
7
52
70

1

i tried that but it didn't solve it, this happen even without using a transaction, you can try it – HB MAAM Sep 01 '13 at 11:20
post the exception call stack please! – Bassam Alugili Sep 01 '13 at 11:26
I found this similar to your problem: http://stackoverflow.com/questions/1836173/entity-framework-store-update-insert-or-delete-statement-affected-an-unexpec – Bassam Alugili Sep 01 '13 at 11:28
it's the same problem with different unknown causes (at least for me) – HB MAAM Sep 01 '13 at 11:55

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

2 Answers2

Linked