Dynamic linq query for date comparison asp.net core

Question

I have a simplified version of dynamic main query as follows :

var items = context.itemsGalore.Where(where).ToList();

The where for the Where condition is built dynamically as below.

if(compare == "Greater")
{
    where = String.Format("{0} > {1}", DueDate, DateTime.ParseExact(dtValue.ToShortDateString(), "MM/dd/yyyy", CultureInfo.InvariantCulture));                            
}
else if(compare == "Lesser")
{
    where = String.Format("{0} < {1}", field, DateTime.ParseExact(dtValue.ToShortDateString(), "MM/dd/yyyy", CultureInfo.InvariantCulture));
}

However I am getting the following error : Operator '<' incompatible with operand types 'DateTime' and 'Int32'

How do I go about fixing the issue?

Why not use lambda expressions? `Where(item => item.DueDate > dtValue)` ? — Chetan, Oct 07 '21 at 08:07
LINQ doesn't work with strings. Use lambda expressions instead. And *don't* use `DateTime.ParseExact(dtValue.ToShortDateString()`. Just pass the DateTime value as a parameter. DateTime is a binary type, it has no format. Using strings instead of dates is a *major* bug, especially when you use an *ambiguous* localized pattern. No-one can say what `11/09/2021` means - September 11 or November 9? Even if you use SQL queries, use parameterized queries instead of constructing queries by string concatenation, and pass strongly typed values — Panagiotis Kanavos, Oct 07 '21 at 08:09
In case you think this is extreme, string concatenation can result in SQL injection ([Bobby Tables](https://xkcd.com/327/)) or serious real-life problems by mixing up the actual dates, [as this unfortunate family found out](https://english.elpais.com/spanish_news/2020-09-08/the-spanish-family-wrongly-accused-of-child-pornography-due-to-a-mistake-reading-a-date.html). Plus, it's *easier* and faster to use parameterized queries — Panagiotis Kanavos, Oct 07 '21 at 08:15
Another issue - `DateTime.ParseExact` uses *the current thread's locale* instead of the InvariantCulture. Unless the application runs in a US locale, this code will *switch* the month and day. The resulting query will compare a field with whatever expression that formatting produces. if it produces `04/07/2021` it will compare the field with the result of an integer division. If it produces `2011-04-07` it's the result of subtraction. In countries where the date separator is `.` this will throw a syntax error (Germany, Russia) — Panagiotis Kanavos, Oct 07 '21 at 08:28
Finally, there's no way EF Core or SQL Server would generate the error `Operator '<' incompatible with operand types 'DateTime' and 'Int32'`. You're using some other library to parse this string into LINQ. What library are you using? — Panagiotis Kanavos, Oct 07 '21 at 08:39
@Chetan The 'where' is only part of the condition. There are other conditions which will concatenate to the date condition and all of them are working fine. I have simplified the query to ensure the problem is accurately represented. — The_Outsider, Oct 07 '21 at 09:13

Milad Dastan Zand · Answer 1 · 2021-10-07T09:23:00.007

0

in the code this section:

else if(compare == "Lesser")
{
    where = String.Format("{0} < {1}", field, DateTime.ParseExact(dtValue.ToShortDateString(), "MM/dd/yyyy", CultureInfo.InvariantCulture));
}

The Error is because of the data type of variable 'field' that is not DateTime.

for example run this code:

using System;
using System.Globalization;
                    
public class Program
{
    public static void Main()
    {
        var compare = "Lesser";
        var where = "";
        var DueDate = DateTime.Now;
        var dtValue = DateTime.Now;
        var field = 1;
        if(compare == "Greater")
        {
            where = String.Format("{0} > {1}", DueDate, DateTime.ParseExact(dtValue.ToShortDateString(), "MM/dd/yyyy", CultureInfo.InvariantCulture));                            
        }
        else if(compare == "Lesser")
        {
            where = String.Format("{0} < {1}", field, DateTime.ParseExact(dtValue.ToShortDateString(), "MM/dd/yyyy", CultureInfo.InvariantCulture));
        }
        
        Console.WriteLine(where);
    }
}

the output is something like this: "1 < 10/07/2021 00:00:00" and you can not use this as a where clause.

edited Oct 07 '21 at 09:23

answered Oct 07 '21 at 08:14

Milad Dastan Zand

1,062
1
10
21

2

This is wrong. In fact, the error is caused because the field *is* a `DateTime` but the parameter isn't. `SomeDate < 11/09/2011` compares the field `SomeDate` with the result of the integer division `11/09/2011`. You can't compare different types in *any* language unless there's a way to convert to a common type. In fact, if this was a valid SQL condition the types would be *the same*. – Panagiotis Kanavos Oct 07 '21 at 08:19
@PanagiotisKanavos it's ok but not in the where clause! I add some sample code in my answer. – Milad Dastan Zand Oct 07 '21 at 08:32
Remove the sentence about dynamic languages because it's not relevant. The problem is that the code results in an invalid SQL condition – Panagiotis Kanavos Oct 07 '21 at 08:36
1

@MiladDastanZand The comment "The Error is because of the data type of variable 'field' that is not DateTime." is not correct. That field is a datetime field present in the table. AS Panagiotis is explaining, the issue is the 10/07/2021 00:00:00 part of the example that you have given. – The_Outsider Oct 07 '21 at 09:31

Dynamic linq query for date comparison asp.net core

1 Answers1