I am having a problem with Entity Framework (Code First) in C # regarding comparing DateTime values. I use the Validity class below (simplified for this example) as a superclass of other entities that must have a certain certainty over time.
public abstract partial class Validity { [Key] public int ID { get; set; } public DateTime? ValidFrom { get; set; } public DateTime? ValidTo { get; set; } public static IQueryable<T> isValidAt<T>(IQueryable<T> query, DateTime time) where T : Validity { return query.Where<T>(c => (!c.ValidFrom.HasValue || time >= c.ValidFrom)
On the last three lines you will find the "Item" class, which we will take as an example. Let's look at this query:
DateTime requestTime = DateTime.Now; var items = from n in Validity.isValidAt(db.Items, requestTime) select n;
This request should only return returned objects of the Item class that are "valid" in the "requestTime". Note that for ValidTo == requestTime, the element should be considered "invalid" (the time with a valid ValidFrom to ValidTo is -exclusive-ValidTo; see the comments in the source code above).
Problem
Actually the -have-result in my "items" result set has ValidTo == requestTime . I just checked it through
Item i= items.FirstOrDefault(); if ((i.ValidFrom.HasValue && i.ValidFrom > requestTime) || (i.ValidTo.HasValue && requestTime >= i.ValidTo)) { // ... SOME ERROR OUTPUT ... }
** NOTE: This error does not occur rarely, but almost all the time in software like .inValidate (requestTime); often calls an invalidate object. **
I manually checked using Microsoft SQL Server Management Studio (Microsoft SQL Server 2008 is used as a backend) using the received LinQ SQL query. I had to declare / install @ p__linq__0, @ p__linq__1 myself (which both mean requestTime) ...
DECLARE @p__linq__0 DATETIME DECLARE @p__linq__1 DATETIME SET @p__linq__0 = '2012-10-23 15:15:11.473' SET @p__linq__1 = '2012-10-23 15:15:11.473'
This works as expected. But if instead I use the value "2012-10-23 15:15:11", I will get the wrong results (as expected). They are similar to those in my program. So I guess the problem is ...
Milliseconds are set in the DateTime database and stored by ValidFrom / ValidTo, including Milliseconds. But I assume that the request does not include the millisecond part of the timestamp for any reason ... The requestTime variable, as ever, has milliseconds.
Unfortunately, I do not know how to check the actual values sent in the request in order to verify this. I only know how to use items.toString () - a method for outputting generated SQL that contains placeholders.
I tried: 1. db.Log = Console.Out; which did not compile due to an error that "db.Log" would not be defined (also autocomplete did not offer "Log"). While db is derived from DbContext. 2. Also casting "objects" in ObjectQuery, and then using .ToTraceString () does not work, the program crashes at run time with an error message that the cast is invalid.
If this is important: I am using .NET 4.0 and EntityFramework.5.0.0.
Questions
- How to register / output full SQL (including placeholder values)?
- How to fix this problem in an elegant way? ... I do not mean a hack that simply subtracts a second from the "time" that is assigned to "ValidTo" in inValidate ()!
Yours faithfully,
Stephen
EDIT (more information)
I checked what happens with the SQL profiler, which seems fine. Timestamps with high (7 characters) prefixes are delivered correctly upon request. BUT: I do not get SELECT, causing the wrong result. So I guessed: it must be some kind of caching. So I set db.SaveChanges(); immediately before my LINQ request. Now I have all the requests in the profiler.
I tried the following code to change the data type in the database. As suggested by Slauma (see https://stackoverflow.com/a/165268/ ).
modelBuilder.Entity<Item>().Property(f => f.ValidFrom) .HasColumnType("datetime2").HasPrecision(3); modelBuilder.Entity<Item>().Property(f => f.ValidTo) .HasColumnType("datetime2").HasPrecision(3);
I reset the whole database before rebooting ...
Result: Successfully use HasPrecision (x); where x is one of 0, 3; (with or without db.SaveChanges () immediately before); BUT: x = 7 works with db.SaveChanges (); just before the request ...
So unfortunately this problem still exists ...
Current workaround
I apply the following method to any DateTime value before assigning it to a property of database objects. It simply rounds the precision of the DateTime to a full second (which I configured in the database). This also applies to any DateTime used to compare time.
Result: this is more of a hack than a solution! I will need to write access functions for all setter methods, so direct assignment cannot happen by accident.
public static DateTime DateTimeDBRound(DateTime time) { DateTime t = time; long fraction = (t.Ticks % TimeSpan.TicksPerSecond); if (fraction >= TimeSpan.TicksPerSecond / 2) { t = t.AddTicks(TimeSpan.TicksPerSecond - fraction); } else { t = t.AddTicks(-fraction); } return t; }