gnychis
gnychis

Reputation: 7555

DateTime LINQ equality and entity framework not working as expected

I have a class with a DateTime member, and I store an instance of that class in to a queue (byte translation), and I also store that same instance in to a database using the Entity Framework. It looks like the following:

// Create instance and set DateTime
MyClass m = new MyClass();
m.CurrentDT = DateTime.Now;

// Store in to database
var db = new EFContext();
db.MyClasses.Add(m);
db.SaveChanges();
db.Dispose();

// Put in to queue
myQueue.Add(m);   // stores as bytes

At some point, I then pull my object back out of the queue as follows:

MyClass mQueueItem = (MyClass)NET.ByteArrayToObject(queueData);

/**
 * Converts a byte array to an object
 */
public static Object ByteArrayToObject(byte[] bytes)
{
    BinaryFormatter formatter = new BinaryFormatter();
    MemoryStream stream = new MemoryStream(bytes);
    object rval = formatter.Deserialize(stream);
    stream.Close();
    return rval;
}

1. Does not work: Then I try to find that exact instance in my Entity Framework database but I consistently get no results returned from this search:

db.MyClasses.Where(dbItem => dbItem.CurrentDT.Equals(mQueueItem.CurrentDT));

2. Does not work: So then I tried to manually go through each item as follows:

foreach(MyClass dbItem in db.MyClasses.ToList())
{
   Console.WriteLine("Is equal: " + dbItem.CurrentDT.Equals(mQueueItem.CurrentDT));
}

... and I of course get false for every one, although I know that one of them is definitely the item I am looking for.

3. Works: Doing the conversion to string actually returns one item as true:

foreach(MyClass dbItem in db.MyClasses.ToList())
{
   Console.WriteLine("Is equal: " + dbItem.CurrentDT.ToString().Equals(mQueueItem.CurrentDT.ToString()));
}

4. Does not work: But finally, running this LINQ query returns no items again:

db.MyClasses.Where(dbItem => dbItem.CurrentDT.ToString().Equals(mQueueItem.CurrentDT.ToString()));

So what am I missing about how DateTime is stored and how can I run equality on it in LINQ? Why don't 1 or 2 work, but 3 works, and why does 4 not work?


Edit: Here is the query from (1)

SELECT 
[Extent1].[PutInQueue] AS [PutInQueue],   // <--- This is the DateTime
[Extent1].[EmailedFrom] AS [EmailedFrom], 
[Extent1].[POName] AS [POName], 
[Extent1].[FC] AS [FC], 
[Extent1].[POSize] AS [POSize], 
[Extent1].[ExcelFilename] AS [ExcelFilename], 
[Extent1].[ExcelData] AS [ExcelData], 
[Extent1].[PDFFilename] AS [PDFFilename], 
[Extent1].[PDFData] AS [PDFData]
FROM [dbo].[IncomingPOes] AS [Extent1]
WHERE [Extent1].[PutInQueue] = @p__linq__0

Here is the resulting query of (4):

SELECT 
[Extent1].[PutInQueue] AS [PutInQueue],   // <--- This is the DateTime
[Extent1].[EmailedFrom] AS [EmailedFrom], 
[Extent1].[POName] AS [POName], 
[Extent1].[FC] AS [FC], 
[Extent1].[POSize] AS [POSize], 
[Extent1].[ExcelFilename] AS [ExcelFilename], 
[Extent1].[ExcelData] AS [ExcelData], 
[Extent1].[PDFFilename] AS [PDFFilename], 
[Extent1].[PDFData] AS [PDFData]
FROM [dbo].[IncomingPOes] AS [Extent1]
WHERE  CAST( [Extent1].[PutInQueue] AS nvarchar(max)) =  CAST( @p__linq__0 AS nvarchar(max))

EDIT: Full Code Example Here is a full code example that demonstrates the issue. After pulling the object back out of the EF database, the DateTimes fail object equality.

https://github.com/gnychis/DbDateTimeTest

[Serializable]
public class MyClasse
{
    [Key]
    public DateTime dateTime { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<MyClasse> MyClasses { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        // Store it in the queue
        MyClasse original = new MyClasse();
        original.dateTime = DateTime.Now;

        // Store it in the database
        var db = new MyContext();
        db.Database.ExecuteSqlCommand("TRUNCATE TABLE dbo.MyClasses");
        db.MyClasses.Add(original);
        db.SaveChanges();

        // Check the database version
        MyClasse dbItem = db.MyClasses.FirstOrDefault();
        Console.WriteLine("DB Item:");
        Console.WriteLine("  * Original: " + original.dateTime.ToString() + " ... Milliseconds: " + original.dateTime.Millisecond);
        Console.WriteLine("  * DB Item: " + dbItem.dateTime.ToString() + " ... Milliseconds: " + dbItem.dateTime.Millisecond);
        Console.WriteLine("  * Equal? " + dbItem.dateTime.Equals(original.dateTime));

        Console.ReadLine();
    }
}

DB Item:

  • Original: 4/30/2015 1:27:29 PM ... Milliseconds: 841

  • DB Item: 4/30/2015 1:27:29 PM ... Milliseconds: 843

  • Equal? False

Upvotes: 1

Views: 1160

Answers (2)

Chris Dunaway
Chris Dunaway

Reputation: 11216

According to MSDN, Datetime values are rounded to increments of .000, .003, or .007 seconds.

It seems as if DateTime is not recommended for new work. They instead recommend using DateTime2 since it has more precision.

Upvotes: 2

jdweng
jdweng

Reputation: 34429

I think the format of the DateTime is different is some cases, or the number stored doesn't match exactly. 3 works because you are not filtering for anything. I date time is stored as a fractional number where the whole number represents the number of day from Jan 1, 1900 and the fraction is the fraction part of the date. 8 Hours is 8/24 = 1/3 which is repeating fraction. DateTime is stored as UTC time and a conversion is preformed based on local timezone. I would carefully look at the string results in 4 and verify that the input string and output strings exactly match.

Upvotes: 0

Related Questions