CODEWITHSUNDEEP
c#linq
coderwill
coderwill

Reputation: 844

convert string to DateTime in linq query with where clause?

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.

Upvotes: 9

Views: 42893

Answers (5)

Avadhesh kumar
Avadhesh kumar

Reputation: 1

You can access in a list then appy filter like below-

var users=db.users.ToList();

var res=users.Where(a=>Convert.ToDateTime(a.returndate)>DateTime.Now).ToList();

Upvotes: -1

kgzdev
kgzdev

Reputation: 2885

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

Upvotes: 14

Ryan
Ryan

Reputation: 746

With current schema you can only get list of all objects and then filter it.

Linq to Entities constructs SQL query which is executed when enumeration is done.

So just get full list, then filter it. Or database schema changing is strongly recommended.

UPDATE

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

But full table is loaded before filtration. So u need to change your field type to datetime. Then u can use simple comparison inside Where.

@NikhilAgrawal also updated his answer in a right way. )

Upvotes: 0

Nikhil Agrawal
Nikhil Agrawal

Reputation: 48580

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.

Upvotes: 4

Sudhakar Tillapudi
Sudhakar Tillapudi

Reputation: 26209

Try This :

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

Upvotes: -1

Related Questions