CODEWITHSUNDEEP
entity-frameworkdatetimerounding-error
Triynko
Triynko

Reputation: 19194

How to compare a date in entity framework to avoid rounding errors?

I have a .NET DateTime value that I write to a SQL Server database "datetime" field (and trust me, I WISH we were just using "datetime2(7)" that matches .NET's DateTime precision exactly, but we're not).

Anyway, I write the entity to the database and that particular field ends up being '2016-03-03 08:55:19.560'.

It's a last processing time, and I'm looking for other records that were processed before that time. When I run an entity framework where clause, it ends up running a statement ending with "@p__linq__0='2016-03-03 08:55:19.5602354'" as the value it's comparing against, which ends up being slightly greater, even though these two values originate from the exact same DateTime instance.

I tried changing the time it's comparing against to an SqlDateTime, but then the lambda doesn't compile because it can't compare a DateTime? to a SqlDateTime. SqlDateTime has comparison methods, but I don't know whether entity framework recognizes the functions.

I can't even cast between the two in entity framework, which just gives the error "Unable to cast the type 'System.DateTime' to type 'System.Data.SqlTypes.SqlDateTime'. LINQ to Entities only supports casting EDM primitive or enumeration types."

Upvotes: 4

Views: 2218

Answers (1)

tschmit007
tschmit007

Reputation: 7800

I face the same problem. In my case I finally value the DateTime fields by using:

public static DateTime RoundedToMs(this DateTime dt) {
    return new DateTime(dt.Ticks - (dt.Ticks % TimeSpan.TicksPerMillisecond), dt.Kind);
}

public static DateTime RoundedToMsForSql(this DateTime dt) {
    DateTime n = dt.RoundedToMs();            
    return new DateTime(n.Year, n.Month, n.Day, n.Hour, n.Minute, n.Second, (n.Millisecond / 10) * 10);
}

And in the business code:

someEntity.SomeDate = dateValue.RoundedToMsForSql();

The point, in my case, is sql datetime has a 3ms precision, so I decided to remove millisecond unit.

Same extension may be used in the queries, well in fact to populate variables used in the queries.

var d = DateTime.Now.RoundedToMsForSql();
var q = from e in ctx.Entities where e.SomeDate <= d;

Upvotes: 2

Related Questions