Comparing dates in a query using LINQ

Question

I have a Linq query that returns type var myQry

var myQry = from ..... 

This big linq returns all the records that I need to filter on. In one of my if conditions, I have a filter that works to check the date. I need to check if the name contains the entered name and exactly matches the date of birth.

I tried this by compiling and executing, but failed

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

Then I tried this, which gave an exception. "The arguments to DbArithmeticExpression must be of a numeric common type."

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

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

Thank you for your time...

Answer 1

In this case, you can use special SQL Server functions using the methods from the SqlMethods class.

The second query can be rewritten as

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

which will be translated into something like

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

where p0, p1 and p2 are parameters.

Answer 2

Try the following:

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

Note that to work above, both BirthDt and searchDt must be valid DateTime values. Now you are only comparing the date part of DateTime values ​​that discard part of the time.

Answer 3

Which operations are supported depends on the structure of the ORM (Nhibernate, EF, etc.), but basically you might think that if the method you use does not have literal translation in SQL, it is very likely that it will not be supported.

For this, the == operator is supported, but not the DateTime.Compare method, or the operator - not supported in DateTime , because it does not have a clear translation.

Always try to stick with the simplest operator and avoid methods; if it still fails, you will have to use Google if this method is supported by your ORM.

Answer 4

I agree with Leniel Macaferi regarding updating the Where clause and comparing dates, not time. For birth, usually the time of birth does not matter. To answer your second question

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

Where() is an extension method that runs on IEnumerable<T> or IQueryable<T> . We can see this by pressing F12 on Where to view the source code:

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

Supported operations are known as predicates. A predicate is a delegate that takes an argument of type TSource and returns a bool that tells 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 a predicate as you like. While it returns bool and accepts 1 parameter of type TSource.

This is usually accomplished by defining the lambda expression that Leniel McAferi made for you.