I've got a problem with Entity Framework (Code First) under C# regarding comparison of DateTime values. I use the below defined class Validity (simplified for this example) as a superclass of of other Entities that shall have a defined validity in time.
public abstract partial class Validity {
[Key]
public int ID { get; set; }
public DateTime? ValidFrom { get; set; }
public DateTime? ValidTo { get; set; }
/**
* @brief This method builds an IQueryable from another IQueryable,
* with added restriction on ValidityFrom/To
*
* An object's validitiy is defined to
* 1. start at timestamp ValidFrom (=inclusive) and
* 2. to end before ValidTo (=exclusive).
* 3. If ValidFrom or ValidTo is NULL, it means to be "unbounded"
* in start or end time (respectively)
*
**/
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) // If ValidFrom != NULL, the given timestamp must be equal or "after" ValidFrom
&& (!c.ValidTo.HasValue || time < c.ValidTo)); // If ValidTo != NULL, the given timestamp must be "before" ValidTo
}
/**
* @brief Shall invalidate the object at timestamp time (implicitly sets validTo attribute).
**/
public void inValidate(DateTime time)
{
ValidTo = time;
}
}
public class Item : Validity {
public string property { get; set; }
}
At the last three lines, you'll find the class "Item" which we'll take as an example. Let's have a look at this query:
DateTime requestTime = DateTime.Now;
var items = from n in Validity.isValidAt(db.Items, requestTime)
select n;
This query should return only return Objects of class Item which are "valid" at "requestTime". Note that for ValidTo == requestTime the Item is to be regarded as "invalid" (the timespan ValidFrom to ValidTo is -exclusive- ValidTo; see comments in source code above).
The Problem
I actually -have- results in my result set "items" having ValidTo == requestTime.
I just checked this via
Item i= items.FirstOrDefault();
if ((i.ValidFrom.HasValue && i.ValidFrom > requestTime)
|| (i.ValidTo.HasValue && requestTime >= i.ValidTo)) {
// ... SOME ERROR OUTPUT ...
}
** NOTE: This error isn't happening seldom, but nearly all the time in the software as .inValidate(requestTime); is often called to invalidate an object. **
I manually checked via Microsoft SQL Server Management Studio (Microsoft SQL Server 2008 is used as backend) using the produced SQL query by LinQ. I had to declare/set @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 actually works as expected. But if I use '2012-10-23 15:15:11' as a value instead, I'll receive wrong results (as expected). They are similiar to those in my program. So I guess that's the problem...
In Database the "DateTime" has Milliseconds defined and ValidFrom/ValidTo are stored including Milliseconds. But I assume that the query doesn't include the milliseconds part of the timestamp for any reason... The variable requestTime how ever has the milliseconds value set.
Unfortunately I don't know how to check the actual values sent in a query to verify this. I only know how to use the items.toString()-Method to output the generated SQL, which contains placeholders.
I tried:
1. db.Log = Console.Out; which didn't compile due to an error that "db.Log" would not be defined (also the auto completion didn't suggest "Log"). Whereas db is derived from DbContext.
2. Also casting "items" to ObjectQuery and then using .ToTraceString() doesn't work, program crashes at runtime with error message that cast is invalid.
If this is important: I use .NET 4.0 and EntityFramework.5.0.0.
Questions
- How to Log/Output the complete SQL (including values of placeholders)?
- How to fix that problem in an elegant way? ...I don't mean a hack that just substracts a second from the 'time' which is assigned to "ValidTo" in inValidate()!
Best regards,
Stefan
EDIT (more details found)
I checked what happens via the SQL profiler, which seems fine. Timestamps with high (7 digits) precisions are supplied correctly when querying. BUT: I don't get the SELECT causing the incorrect result. So I guessed: it must be some caching. So I put a db.SaveChanges(); directly before my LINQ query. Now I got all queries in the profiler.
I tried the following code to change the datatype in database. As it was suggested by Slauma (see https://stackoverflow.com/a/8044310/270591).
modelBuilder.Entity<Item>().Property(f => f.ValidFrom)
.HasColumnType("datetime2").HasPrecision(3);
modelBuilder.Entity<Item>().Property(f => f.ValidTo)
.HasColumnType("datetime2").HasPrecision(3);
I dropped the whole database before restart...
Result: No success using HasPrecision(x); where x is one of 0, 3; (with or without db.SaveChanges() directly before); BUT: x = 7 fairly works with db.SaveChanges(); directly before the query...
So, unfortunately this problem is still existent...
Current Workaround
I apply the following method to any DateTime value before assigning it to a database objects property. It just rounds the DateTime to full seconds precision (which I configured at the DB). Also this is applied to any DateTime used for comparison.
Result: This is more a hack than a solution! I will need to write access functions for all setter methods, so that a 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;
}
