实体框架格式化DateTime SQL参数(不带毫秒)以实现乐观并发 [英] Entity Framework formats DateTime SQL parameter without milliseconds for optimistic concurrency

问题描述

我试图在实体框架中使用具有乐观并发性的DateTime LastModifiedDate列(可能会将其升级到DateTime2.)我已将并发模式设置为Fixed.但是，当我检索一个实体时，更改一列并尝试保存，并发并发异常.

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.

存储的LastModifiedDate为2017-01-04 21:16:55.283，但请查看为更新生成的SQL实体框架:

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)

请注意，它正在为@2传递格式化的DateTime字符串，该字符串不包含毫秒.好吧，如果它没有传递与检索到的相同的值，那当然不匹配！我已验证在运行时，.NET DateTime确实包含0.283秒.请告诉我，有一种方法可以传递全部价值.为什么它会以这种方式运行，我如何将其更改为包含毫秒?

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?

推荐答案

-- @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'

发生的事情是datetime2(7)值不是往返转换为datetime和从datetime转换回来的值.最简单的解决方法是对表列使用datetime2(7).如果要将列用于乐观并发控制，则无论如何都需要额外的精度.

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.

是这样的:

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();
        }
    }
}

这篇关于实体框架格式化DateTime SQL参数(不带毫秒)以实现乐观并发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！