CODEWITHSUNDEEP
c#linqentity-frameworkdatetime
Rodrigo Elias
Rodrigo Elias

Reputation: 783

Error using EF6 DbFunctions.TruncateTime comparing DateTime

I`m trying to retrieve some data from a data range using the following code:

    var rotas = db.X.Where(r => r.DataDaExecucao != null)
    .Where(r => System.Data.Entity.DbFunctions.TruncateTime(r.Date.Value) >=         System.Data.Entity.DbFunctions.TruncateTime(startDateTime))
    .Where(r => System.Data.Entity.DbFunctions.TruncateTime(r.Date.Value) < System.Data.Entity.DbFunctions.TruncateTime(endDateTime))
    .Join(db.T, r => r.Id, t => t.X_Id.Value,
    (r, t) => new
    {
    id = r.Id,
    start = r.Date.Value.ToString("s"),
    end = r.Date.Value.AddDays(1).ToString("s"),
    title = t.Z.Name,
    allday = false
    }).ToList();

"Date" properties are Nullable< DateTime>.

I`m getting the following error message:

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

Exception Details: System.NotSupportedException: LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

Also, I don`t have the System.Data.Entity.dll assembly referenced in my csproj.

Ideas?

Thank you, in advance.

Upvotes: 0

Views: 5149

Answers (2)

Yuliam Chandra
Yuliam Chandra

Reputation: 14640

You can change the anonymous type using SqlFunctions where it will also generate the sql query.

In your case you use ToString("s"), meaning you want to get the date part of second that can be replaced by SqlFunctions::DatePart. And Date::AddDays can be replaced by SqlFunctions::DateAdd.

new
{
    id = r.Id,
    start = SqlFunctions.DatePart("s", r.Date),
    end = SqlFunctions.DatePart("s", SqlFunctions.DateAdd("d", 1, r.Date)),
    title = t.Z.Name,
    allday = false
}

Upvotes: 1

saille
saille

Reputation: 9181

DateTime.ToString() cannot be converted into a SQL statement by LINQ to Entities. e.g.

start = r.Date.Value.ToString("s")

The thing to do is call .ToList() to force the LINQ to Entities to execute its underlying SQL query. That way the remainder of the LINQ statement will use LINQ to Objects (in-memory query of a collection of objects).

In your case I would break the LINQ statement into 2 parts:

  1. The first half which queries the DB with LINQ to Entities SQL generation, and calls .ToList() at the end
  2. The second half which runs LINQ to Objects to do the in-memory part.

Upvotes: 1

Related Questions