convert string to DateTime in linq query with where clause?

Question

I am try to get user list using EF. and I have one filed in the table it's return date as a string data type. Now I'm compare this return date to Today's date then I'm getting error.so any one idea how can do that convert string to datetime in C#.

Here's the query:

var res = db.users
  .Where(a => Convert.ToDateTime(a.returndate) > DateTime.Now)
  .ToList(); // but here not getting list

So it's possible in EF string to DateTime conversion. If anyone has an idea please let me know.

Answer 1

You can not use Convert.ToDatetime method in LINQ to Entities, so first get data from db then use method on list

var res = db.users.ToList().Where(a => DateTime.ParseExact(a.returndate,"dd-MM-yyyy",CultureInfo.InvariantCulture) > DateTime.Now).ToList();

NOTE: If you can change your columns data type to DateTime type then you can compare them in LINQ To Entities, otherwise you should do as above

Answer 2

As per your comments to question you need to do

var res = db.users.ToList().Where(a => DateTime.ParseExact(a.returndate, "dd-MM-yyyy", CultureInfo.InvariantCulture) > DateTime.Now);

In your case Convert.ToDateTime(string str) is not working as passed DateTime format is different from your System's format.

BTW,

....EF string to DateTime conversion.

Never ever store DateTime strings in database. Databases have corresponding DateTime types where you can store information as DateTime itself. That way when bringing values you don't have to repeat the code.