How can I compare a nullable type date using user entered date?

c# datetime entity-framework entity-framework-6

Question

I can't find how to compare a date entered by a user to a date on a table created by EF6. I'm using LINQ Extensions to search the DB and have tried multiple suggestions that compile, but fail during execution.

User Input:

DateTime userdate;
DateTime.TryParse(search.Value, out userdate);

Attempts so far:

a = dbContext.Messages.Where(x => x.Date.Date == userdate.Date); // doesn't compile
a = dbContext.Messages.Where(x => x.Date.Value.Date == userdate.Date); // error # 4
a = dbContext.Messages.Where(x => x.Date.ToString() == userdate.Date.ToString()); // issue #2
a = dbContext.Messages.Where(x => x.Date.Value.ToString() == userdate.Date.ToString()); // issue #2
a = dbContext.Messages.Where(x => Object.Equals(x.Date, userdate.Date)); // throws error #1
a = dbContext.Messages.Where(x => Object.Equals(x.Date.Date, userdate.Date)); // throws error #1

I've tried searching this a million times and find solutions that compile, yet always throw an error when executed.

There was a version of the ToString() that worked, but it generated a long date string such as May 12, 2005 00:00:00 and trying to turn my userdate into a long date to compare just seems like it would create too much overhead when searching through millions of records.

I'm fresh out of ideas here.

Issues:

  1. Unable to cast the type 'System.Nullable``1[[System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.

  2. Doesn't find any matches, even with exact datetime

  3. Magically works all of a sudden.

  4. The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

Final solution Based on the selected answer, I found that the SqlFunctions class has a .DateDiff method which maps to the T-SQL DateDiff function. I used that to count the number of days between the two dates and selected only the ones that returned 0. That allowed me to compare the dates without having to do any conversion on the SQL side.

1
1
3/29/2015 6:32:35 PM

Accepted Answer

Some .NET operations can not be implicitly converted to equivalent SQL operations, for these you need to use the methods provided in System.Data.Entity.DbFunctions. In your specific example, you should try using the TruncateTime method instead of accessing .Date property of the DateTime instance.

Example:

var date = userDate.Date;
dbSet.Where(x => DbFunctions.TruncateTime(x.Date) == date);

Edit: Alternatively, the SQL server does not necessarily support the same range for datetime-values as .NET does, so if your string parse fails and you get DateTime.MinValue it may not be a supported datetime value in your SQL server.

3
3/29/2015 4:35:46 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow