Reputation: 7555
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
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
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