Linq to Entities SqlFunctions.DatePart

Question

This code is producing an error:

void GetLog()
{
    List<CAR_STATUS_LOGS> logs = null;
    using (TESTEntities ctx = new TESTEntities())
    {
        logs = 
            ctx.CAR_STATUS_LOGS
                .Where(a => SqlFunctions.DatePart("DAY", a.TIMEMARK) == 1)
                .ToList();
    }
}

The error is this:

LINQ to Entities does not recognize the method 'System.Nullable`1[System.Int32] DatePart(System.String, System.Nullable`1[System.DateTime])' method, and this method cannot be translated into a store expression.

I can't see what I am doing wrong with the SqlFunctions.DatePart. The column TIMEMARK is a Sqlserver DateTime type.
Any ideas?

Could you maybe look at the results, if you do (instead of the Where) : .Select(a => SqlFunctions.DatePart("day", a.TIMEMARK)).ToList() — Raphaël Althaus
@RaphaëlAlthaus what is the use for null check of TIMEMARK I mean the second argument is nullable in datepart I guess — Neel

Alexandre Alexandre · Accepted Answer · 2015-03-13T20:43:55

You can use System.Data.Entity.SqlServer.SqlFunctions from assembly EntityFramework.SqlServer.dll (Entity Framework 6.1.3), example:

myTable.Where (w => 
    (System.Data.Entity.SqlServer.SqlFunctions.DatePart("weekday", w.Data).Value == 1);

Linq to Entities SqlFunctions.DatePart

4 Answers