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

Question

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 ?

EDIT

The trouble is that I need compare just date, not time ...

Answer 1

Try this

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 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 <= 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

Answer 2

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();

where startDate and endDate are both DateTime.

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.