In LINQ I want make thw follow query:
_tareaRepositorio.ObtenerTodo() .Where( x => Convert.ToDateTime(x.FechaCreacion.ToString("d")) >= fechaInicial && Convert.ToDateTime(x.FechaCreacion.ToString("d")) <= fechaFinal).ToList();
But, at the moment of execute the query appears the follow mistake:
LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)' method, and this method cannot be translated into a store expression.
How I can fix that ?
The trouble is that I need compare just date, not time ...
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 are getting is because the
Convert.ToDateTime is a .NET method that cannot be converted into SQL. Normally this should be done after the query has been materialized (i.e. using
.ToList() before the
Where) but in your particular case it is unnecesary since
DateTime objects can be compared using
<= and Entity Framework can convert it to SQL successfully
Now if you only want to compare the Date part of the Datetime you can use the method
DbFunctions.TruncateTime() which lives inside
System.Data.Entity namespace. This method will allow EF to correctly truncate the date in SQL
If you want to get entities between two dates, compare as dates:
var result = content.Table.Where(x => x.date >= startDate && x.date <= endDate) .ToList();
endDate are both
Converting to strings means you are at the mercy of the current culture (locale) settings. With the invariant culture the default date format is American, so they do not compare lexicographically with any useful sense.