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...

Solution 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.

Solution 2

Try this:

myQry.Where(x => x.FirstName.Contains(strName) &&
x.BirthDt.Date == searchDt.Date).ToList()

Note that for the above to work, both BirthDt and searchDt must be valid DateTime values. You're now comparing only the Date part of the DateTime values discarding the Time part.

Solution 3

I agree with Leniel Macaferi regarding updating the Where clause and comparing dates rather than datetimes. For a birthdate, usually the time of birth is not relevant. To answer your second question

What kind of operations are not allowed in the where clause of a LinQ query?

Where() is an extension method that works on IEnumerable<T> or IQueryable<T>. We can see this by hitting F12 on Where to take a look at the source code:

public static IEnumerable<TSource> Where<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate);

The supported operations are known as predicates. A predicate is a delegate that takes an argument of type TSource and returns a bool telling us whether the condition is a match or not. This can be seen in the code above in the second parameter: Func<TSource, bool> predicate

You can define the predicate to be whatever you want. So long as it returns a bool and accepts 1 parameter of type TSource.

This is typically achieved by defining a lambda expression, which is what Leniel Macaferi did for you.

Solution 4

What operations are supported depends on the ORM framework, (Nhibernate, EF, etc), but basically you can consider that if the method you are using does not have a literal translation to SQL it is very likely that it will not be supported.

That's why the operator == is supported but not DateTime.Compare method, or operator - in DateTime is not supported since it does not have a clear translation.

Always try to stick to the simplest operator and avoid methods, if that still fails you will have to Google around if that specific method is supported by your ORM.

View more solutions