LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)' method

Question

I am trying to convert one application to EntityFrameWork codefirst. My present code is

 string sFrom  ="26/12/2013";
 select * FROM Trans where  CONVERT(datetime, Date, 105) >=   CONVERT(datetime,'" + sFrom + "',105) 

And i tried

 DateTime dtFrom = Convert.ToDateTime(sFrom );
TransRepository.Entities.Where(x  =>Convert.ToDateTime(x.Date) >= dtFrom) 

But I got an error like this

LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)' method

Please help Thanks in advance

Answer 1

when you do this:

TransRepository.Entities.Where(x  =>Convert.ToDateTime(x.Date) >= dtFrom) 

LINQ to Entities cannot translate most .NET Date methods (including the casting you used) into SQL since there is no equivalent SQL. What you need to do is to do below:

 DateTime dtFrom = Convert.ToDateTime(sFrom );
  TransRepository
 .Entities.ToList()//forces execution
 .Where(x  =>Convert.ToDateTime(x.Date) >= dtFrom) 

but wait the above query will fetch entire data, and perform .Where on it, definitely you don't want that,

simple soultion would be this, personally, I would have made my Entity field as DateTime and db column as DateTime

but since, your db/Entity Date field is string, you don't have any other option, other than to change your field in the entity and db to DateTime and then do the comparison

Answer 2

Why is your date column a string? Shouldn't it be a DateTime?

Regardless, if you attempt to perform conversions using .NET functions in a .Where statement against a repository of entities, you'll get that error.

Your best option would be to change that column from a string to a DateTime and proceed from there. If you do, the .Where statement would be:

DateTime dtFrom = Convert.ToDateTime(sFrom );
var something = TransRepository.Entities.Where(x  => x.Date >= dtFrom) ;