Entity Framework formats DateTime SQL parameter without milliseconds for optimistic concurrency

Question

I'm trying to use a DateTime LastModifiedDate column with optimistic concurrency in Entity Framework (will likely upgrade it to DateTime2.) I've set the Concurrency Mode to Fixed. But when I retrieve an entity, change a column and try to save, get a concurrency exception.

The stored LastModifiedDate is 2017-01-04 21:16:55.283 but look at the SQL Entity Framework is generating for the update:

UPDATE [dbo].[Facilities] 
SET [Password] = @0 
WHERE (([pk_FacilityID] = @1) AND ([LastModifiedDate] = @2)) 

-- @0: 'bz0dkK+smlat9psrIrbyXkxjpcXcDK1DeUiha7jCRkU=' (Type = String, Size = 255) 

-- @1: '6801bdcf-266d-46bd-b15e-dac21116208d' (Type = Guid) 

-- @2: '1/4/2017 9:16:55 PM' (Type = DateTime2)

Notice it's passing a formatted DateTime string for @2 which does not include milliseconds. Well of course it doesn't match if it's not passing in the same value it retrieved! I've verified that at runtime, the .NET DateTime does include the 0.283 seconds. Please tell me there's a way to pass in the full value. Why does it behave this way and how can I change it to include milliseconds?

Answer 1

-- @2: '1/4/2017 9:16:55 PM' (Type = DateTime2)

That's not the actual parameter value sent. That's just the logging, and it leaves off the fractional seconds. There is a problem, but that's not it.

If you profile you should see something like this, that shows the details of the parameter type and value.

exec sp_executesql N'UPDATE [dbo].[Facilities]
SET [Name] = @0
WHERE (([FacilityId] = @1) AND ([LastModified] = @2))
',N'@0 nvarchar(max) ,@1 int,@2 datetime2(7)',@0=N'newName',@1=1,@2='2017-08-31 15:45:55.3030000'

What's happening is the datetime2(7) value is not round-trip converting to and back from datetime. And the easiest fix is just to use datetime2(7) for your table column. If you want to use the column for optimistic concurrency control, you need the extra precision anyway.

So something like this:

using System;
using System.Linq;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;

namespace Ef6Test
{
    public class Facility
    {
        public int FacilityId { get; set; }

        public string Name { get; set; }

        [ConcurrencyCheck(),Column(TypeName ="datetime2")]
        public DateTime LastModified { get; set; }
    }

    class Db : DbContext
    {
        public DbSet<Facility> Facilities { get; set; }


        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }

    }
    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<Db>());


            using (var db = new Db())
            {
                var f = db.Facilities.Add(new Facility() { LastModified = DateTime.Now, Name = "Faclity1" });
                db.SaveChanges();
            }
            using (var db = new Db())
            {
                var f = db.Facilities.First();
                f.Name = "newName";
                db.SaveChanges();
            }


            Console.ReadKey();
        }
    }
}