如何更改Entity Framework如何为Datetime生成SQL精度 [英] How to change how Entity Framework generates SQL precision for Datetime

问题描述

我有一个表，使用ID& DateTime列是pk，但是当我尝试通过Entity Framework这样更新数据时：

I have a table use id & DateTime column be the pk, but when I try to update data by Entity Framework like this:

using (Entities context = new Entities())
{
    var item = (from item in context.BatchData
                where item.Id == 2
                select item ).FirstOrDefault();

    item.Title = "EF6TEST";

    context.SaveChanges();
}

我收到错误

存储更新，插入或删除语句影响了意外的行数（0）。

Store update, insert, or delete statement affected an unexpected number of rows (0).

记录完SQL后，我现在知道原因了。

After I recorded the SQL, I know the reason now.

SQL看起来像这样

'update [dbo].[BatchData]
set [BatchData_Title] = @0
where (([BatchData_Id] = @1) and ([BatchData_CreatedDateTime] = @2))

select [BatchData_Rowversion]
from [dbo].[BatchData]BatchUploadData
where @@ROWCOUNT > 0 and [BatchData_Id] = @1 and [BatchData_CreatedDateTime]     = @2',
N'@0 varchar(30),@1 tinyint,@2 datetime2(7)',
@0='EF6TEST',@1=1,@2='2017-09-16 11:29:35.3720000'

所以，原因是SQL中的 BatchData_CreatedDateTime 参数是 @ 2 ='2017-09-16 11：29：35.3720000'，精度为7，应为 @ 2 ='2017-09-16 11：29：35.372'。

So, the reason is BatchData_CreatedDateTime parameter in the SQL is @2='2017-09-16 11:29:35.3720000', the precision is 7 and it should be @2='2017-09-16 11:29:35.372' .

这是我的问题，如何解决？

And here is my question, How to fix it?

推荐答案

您可以使用 IDbInterceptor 更改所需的数据，这是一个将参数类型从 DateTime2 更改为 DateTime 的拦截器示例，您可以扩展它以在特定字段上使用DB / DbCommand参数。

You can use IDbInterceptor to change required data, here is an example of interceptor that changes type of parameters from DateTime2 to DateTime, you can extend it to use it on a specific fields of your DB / DbCommand parameters.

public class DateInterceptor : IDbInterceptor, IDbCommandInterceptor
{
    public void ReaderExecuting(DbCommand command, 
        DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        var dateParameters = command.Parameters.OfType<DbParameter>()
            .Where(p => p.DbType == DbType.DateTime2);
        foreach (var parameter in dateParameters)
        {
            parameter.DbType = DbType.DateTime;
        }
    }

要使用它，请添加 DbInterception将（new DateInterceptor（））; 添加到dbContext类的 OnModelCreating 的末尾

To use it add DbInterception.Add(new DateInterceptor()); into the end of OnModelCreating of your dbContext class

生成的SQL将从

@ 2 datetime2（7）'，@ 0 = 0，@ 1 = 1，@ 2 ='2017-09-24 14：41：33.7950485'

@2 datetime2(7)',@0=0,@1=1,@2='2017-09-24 14:41:33.7950485'

至

@ 2 datetime'，@ 0 = 0，@ 1 = 1，@ 2 ='2017-09-24 14：40：32.327'

@2 datetime',@0=0,@1=1,@2='2017-09-24 14:40:32.327'

这篇关于如何更改Entity Framework如何为Datetime生成SQL精度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！