Marsen Lin Marsen Lin - 5 months ago 66
C# Question

How to change how Entity Framework generates SQL precision for Datetime

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


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

Answer Source

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'


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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download