How to change how Entity Framework generates SQL precision for Datetime

user_1244100 0
Viewed 1433

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

I get an error

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

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

The SQL looks like this 

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

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?

datetime entity-framework sql-server c#
2 Answers

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

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

Generated SQL will be changed from

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

to

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

user_5144490
user_72977000
Related