Issues with Entity Framework Concurrency Token DataTime type

Question

I have an issue with concurrency token of DateTime. Here's a simple way to reproduce the problem. Have one entity:

public class Employee
{
    public int EmployeeID { get; set; }
    public string Name { get; set; }
    [ConcurrencyCheck]
    public DateTime LastModified { get; set; }
}

A trivial DbContext:

public class MyContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }
}

And the following code:

Employee orig;

//  Create a row (insert)
using (var context = new MyContext())
{
    orig = new Employee
    {
        Name = "Mike",
        LastModified = DateTime.Now
    };
    context.Employees.Add(orig);

    context.SaveChanges();
}
//  Update the row, passing the right concurrency token
using (var context = new MyContext())
{
    var clone = new Employee
    {
        EmployeeID = orig.EmployeeID,
        Name = "Suzanne",
        //  Pass the concurrency token here
        LastModified = orig.LastModified
    };
    context.Employees.Attach(clone);
    //  Mark the entity as modified to force an update
    context.Entry(clone).State = EntityState.Modified;

    //  Boom!  Currency exception!
    context.SaveChanges();
}

Basically, I create an employee, then update it. Bang! I look at the update statement generated on SQL (Profiling):

exec sp_executesql N'update [dbo].[Employees]
set [Name] = @0, [LastModified] = @1
where (([EmployeeID] = @2) and ([LastModified] = @3))
',N'@0 nvarchar(max) ,@1 datetime2(7),@2 int,@3 datetime2(7)',@0=N'Suzanne',@1='2012-02-21 
12:06:30.0141536',@2=0,@3='2012-02-21 12:06:30.0141536'

The statement seems sound to me, but it fails, i.e. it modifies zero row as if ([LastModified] = @3) failed.

I suspect a 'precision problem', i.e. the number of digits mismatched with the one stored. Could it be a mismatch between DateTime representation in .NET and SQL?

I've tried using System.Data.SqlTypes.SqlDateTime instead of DateTime in my Poco class, hoping this would carry the right precision, but I wasn't able to map it, EF always had the property unmapped.

Solutions?

(I misread at first and posted a wrong answer, please ignore that if you read it.) Since `LastModified` has the `ConcurrencyCheck` attribute, *if* `DateTime` has more precision than the SQL Server data type, it should be truncated after `SaveChanges`. If `DateTime` has *less* precision, then the value should be stored exactly. Either way, after `SaveChanges`, the `LastModified` property should match exactly what is stored in the database. Could you check what exactly is stored by retrieving the date without EF? — , Feb 21 '12 at 18:23
I have a similar problem where my column type is `datetime` (not `datetime2`), and EF still sends the parameters in the update query as `datetime2` for some reason. How did you end up solving this? — sinelaw, Aug 17 '12 at 17:34
Update - I solved my problem by setting `ProviderManifestToken` to `2005`. I'm using Code First, so the fix involved creating my own `DbModelBuilder`. See [this answer](http://stackoverflow.com/a/12060518/562906) — sinelaw, Aug 21 '12 at 22:37

score 5 · Answer 1 · answered Feb 22 '12 at 20:34

I found the problem! Actually, there are two problems here: a technical one and a semantic one.

The technical problem is that EF, for whatever reason, sends System.DateTime as datetime(2) SQL type to SQL. By default, it does map System.DateTime as datetime though. I actually didn't succeed to have EF create the DB with datetime(2) despite forcing the SQL type to datetime(2). But if you change it after the fact, it solves the problem. So the problem was really a precision problem.

The semantic problem is that the entire doesn't make sense if you think about it. A concurrency token is something you need to pass to SQL to prove you were the last one to read the table. But a concurrency token therefore needs to be updated each time a row is updated. One precludes the other: if you try to update the LastModified to DateTime.Now, you'll have a concurrency exception since the concurrency token isn't the one stored in the row!

So despite finding a solution to the technical problem, this entire scheme doesn't make sense.

... unless! You find a way to update the LastModified column without using EF. You could have a trigger for instance. Typically you wouldn't want to go there though.

That's why you're not supposed to update the token yourself. If EF knows that it's a concurrency token, it should (and will) perform an appropriate query: `UPDATE ... SET token = [new value] WHERE token = [old value]`. There's no semantic problem here. — sinelaw, Dec 19 '13 at 15:14

Issues with Entity Framework Concurrency Token DataTime type

1 Answers1