Comparing dates in query using LINQ

Question

I have a Linq query that returns to a var type myQry

var myQry = from .....

This is a large linq returns all the records that I need to filter further. In one of my if-conditions I have a filter that runs like this to check against date. I need to check if name contains the name entered and matches the birthdate exactly.

I tried this which compiled and ran, but did not work correctly

myQry.Where(x => x.FirstName.Contains(strName) && DateTime.Compare( x.BirthDt, searchDt)>=0).ToList()

Then I tried this which gave threw an exception "DbArithmeticExpression arguments must have a numeric common type"

myQry.Where(x => x.FirstName.Contains(strName) && (x.BirthDt- searchDt).Days == 0).ToList();

For such kind of a situation when I use a where clause on my query, what would be the best way to do a date comparison? What kind of operations are not allowed in the where clause of a LinQ query?

Thanks for your time...

Answer 1

In this case you may want to use SQL Server specific functions using methods from the SqlMethods class.

Your second query could be rewritten as

myQry.Where(x => x.FirstName.Contains(strName) && 
    SqlMethods.DateDiffDay(x.BirthDt, searchDt) == 0).ToList()

which will be translated to something like

SELECT ... FROM Table WHERE FirstName 
    LIKE '@p0' AND DATEDIFF(Day, BirthDt, @p1) = @p2

where p0,p1 and p2 are parameters.