LINQ to Entities DateTime is inconsistent

Question

I have the following C# code that creates a DateTime, enters it into a table, and then queries that table:

DateTime date = DateTime.Now;

DateTest newRecord = new DateTest {
    dateColumn = date
};

db.DateTest.AddObject(newRecord);
db.SaveChanges();

IQueryable<DateTest> records =
    from d in db.DateTest
    select d;

If I break the code at this point, and take a look at the objects in the debugger, I get this for the date object:

Date    {11/22/2011 12:00:00 AM}    System.DateTime
Day 22  int
DayOfWeek   Tuesday System.DayOfWeek
DayOfYear   326 int
Hour    8   int
Kind    Local   System.DateTimeKind
Millisecond 345 int
Minute  59  int
Month   11  int
Second  33  int
Ticks   634575491733450602  long
TimeOfDay   {08:59:33.3450602}  System.TimeSpan
Year    2011    int

And I get this for the record retrieved from the table:

Date    {11/22/2011 12:00:00 AM}    System.DateTime
Day 22  int
DayOfWeek   Tuesday System.DayOfWeek
DayOfYear   326 int
Hour    8   int
Kind    Unspecified System.DateTimeKind
Millisecond 347 int
Minute  59  int
Month   11  int
Second  33  int
Ticks   634575491733470000  long
TimeOfDay   {08:59:33.3470000}  System.TimeSpan
Year    2011    int

As you can see, they're off by a couple milliseconds.

Can anyone explain why this is, and how I can fix it? I need to be able to query for records exactly matching a DateTime object in memory, but this behaviour is causing my queries to come up empty handed.

Answer 1

The resolution of the DateTime field in SQL Server is different from the one of the DateTime .NET class.

From MSDN - datetime (Transact-SQL):

Accuracy: Rounded to increments of .000, .003, or .007 seconds

So, in your case, the milliseconds get rounded up to the .007, giving .3470000 instead of .3450602.

The DateTime2 SQL Server datatype has a resolution of 100 nano seconds, like .NET, so may be a suitable replacement.

Answer 2

I think it is because you are using a DateTime column in the database. It does not have the same precision as the DateTime type in .NET. Try using column type DateTime2 in the database instead.