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.
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.
Unable to cast the type 'System.Nullable``1[[System.DateTime, mscorlib, Version=18.104.22.168, Culture=neutral, PublicKeyToken=b77a5c561934e089]]' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.
Doesn't find any matches, even with exact datetime
Magically works all of a sudden.
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.
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
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.