CODEWITHSUNDEEP
entity-frameworkef-code-firstcode-first
Vincent-Philippe Lauzon
Vincent-Philippe Lauzon

Reputation: 879

Issues with Entity Framework Concurrency Token DataTime type

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?

Upvotes: 6

Views: 2866

Answers (1)

Vincent-Philippe Lauzon
Vincent-Philippe Lauzon

Reputation: 879

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.

Upvotes: 6

Related Questions