Compare date in linq

Question

I have a string 31-10-2014 wich i want to compare with a value in the database.

public ActionResult SearchResult(string searchDate)
{
    var bookings = from m in db.Bookings
                   select m;
    if (!String.IsNullOrEmpty(searchDate))
    {
        bookings = bookings.Where(s => s.Date1.CompareTo( DateTime.ParseExact(searchDate, "dd-mm-yyyy", CultureInfo.InvariantCulture)) >= 0);
    }
    return View(bookings);
}

the comparing keeps failing, How should i compare an input date to a database value (i left the try catch for validating the input to be an dattime so the example is more clear)

I get the message (even without searchData):

Linq doesn't recognise ParseExact method.

the error message will help up but i think the issue is that EF does not know how to convert that into an SQL command. — workabyte, Oct 22 '14 at 21:31

score 6 · Accepted Answer · answered Oct 22 '14 at 21:33

It's trying to compile DateTime.ParseExact into SQL, and failing. All you need is to define a variable and put the value into that variable, then use the variable in your query:

if (!String.IsNullOrEmpty(searchDate))
{
    DateTime dt = DateTime.ParseExact(searchDate, "dd-mm-yyyy", CultureInfo.InvariantCulture);
    bookings = bookings.Where(s => s.Date1 >= dt);
}

score 2 · Answer 2 · edited May 23 '17 at 11:58

It depends on what the variable db is. If it's a LINQ to SQL DataContext or EF DBContext, you can't use the methods you are using on date; there is no support. See this. There are SQL functions to work around that issue.

If you want raw SQL support for Dates, use the EntityFunctions class, which lets you do date comparisions. CompareTo is also not supported, so you'd have to use traditional greater than.

score 0 · Answer 3 · answered Oct 22 '14 at 21:30

0

You have a - instead of an = in your code in Line #7.

Here's the right code.

public ActionResult SearchResult(string searchDate)
{
    var bookings = from m in db.Bookings
                   select m;
    if (!String.IsNullOrEmpty(searchDate))
    {
        bookings = bookings.Where(s => s.Date1.CompareTo( DateTime.ParseExact(searchDate, "dd-mm-yyyy", CultureInfo.InvariantCulture) >= 0);
    }
    return View(bookings);
}

answered Oct 22 '14 at 21:30

Shiva

20,575
14
82
112

2

It won't translate into SQL anyway. – abatishchev Oct 22 '14 at 21:32

score 0 · Answer 4 · answered Oct 23 '14 at 05:17

To make it in a simpler way, try this:

bookings = bookings.ToList().Where(s => s.Date1.CompareTo( DateTime.ParseExact(searchDate, "dd-mm-yyyy", CultureInfo.InvariantCulture)) >= 0);

Put it first in a list so that it will not take the DateTime.ParseExact as SQL statement since there's no DateTime.ParseExact in SQL.

Hope this helps

Compare date in linq

4 Answers4