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

Question

In LINQ, I want to make the following query:

_tareaRepositorio.ObtenerTodo() .Where( x => Convert.ToDateTime(x.FechaCreacion.ToString("d")) >= fechaInicial && Convert.ToDateTime(x.FechaCreacion.ToString("d")) <= fechaFinal).ToList(); 

But at the time of the query, the following error appears:

LINQ to Entities does not recognize the 'System.DateTime ToDateTime (System.String)' method, and this method cannot be translated into a storage expression.

How can i fix this?

EDIT

The problem is that I only need to compare the date, not the time ...

Answer 1

try it

EDIT: Add DbFunctions.TruncateTime() to get the desired effect

 _tareaRepositorio.ObtenerTodo() .Where( x => DbFunctions.TruncateTime(x.FechaCreacion) >= fechaInicial && DbFunctions.TruncateTime(x.FechaCreacion) <= fechaFinal) .ToList(); 

The exception you get is that Convert.ToDateTime is a .NET method that cannot be converted to SQL. Usually this should be done after the request has been materialized (i.e. using .ToList() before Where ), but in your particular case this is unnecessary since DateTime objects can be compared using >= and <= and the Entity Framework can successfully convert it to SQL

Now, if you want to compare only the date in Datetime, you can use the DbFunctions.TruncateTime() method, which lives in the System.Data.Entity namespace. This method will allow EF to correctly truncate the date in SQL

Answer 2

If you want to get objects between two dates, compare them with dates:

 var result = content.Table.Where(x => x.date >= startDate && x.date <= endDate) .ToList(); 

where startDate and endDate are DateTime .

Converting to strings means that you are at the mercy of the current culture settings (locale). With the invariant culture, the default date format is American, so they do not compare lexicographically with any useful meaning.

Answer 3

Return the result with AsEnumerable (), then convert it to a list.

This will work, I will face the same problem as you, this solution may work.