Linq To Sql: The member Date has no supported translation to SQL

Question

Consider a class that's used to represent the results of a GroupBy(). The goal of the code is to replace a stored procedure that would have grouped and counted a bunch of rows by the Created datetime field.

public class Statistic
{
    public int NumRecords { get; set; }
    public DateTime Date { get; set; }
}

Here's the code raising an exception/warning:

//returning an IEnumerable<Statistic>
return db.MyTable
            .GroupBy(x => x.CreatedOn.Date ) //precision of date, not time.
            .Select(x => new Statistic(x.Key, x.Count()) ) //custom object
            .OrderBy(x => x.Date);

The exception:

The member Date has no supported translation to SQL

When I refactor the code to load into a var, the exception/warning is generated on the OrderBy().

Question: How can I avoid this exception using Linq To Sql?

Answer 1

I've run into this before when using the non-default constructor. If you use an object initializer instead, Linq to Sql can figure out the mapping:

return db.MyTable
            .GroupBy(x => x.CreatedOn.Date ) //precision of date, not time.
            .Select(x => new Statistic{Date = x.Key, NumRecords = x.Count()} ) //custom object
            .OrderBy(x => x.Date);

Answer 2

It turns out that the query wasn't being executed and loaded into a variable when I had expected it.

The following will evaluate and run properly as expected.

IEnumerable<Statistic> stats = db.MyTable           
        .GroupBy(x => x.CreatedOn.Date )
        .Select(x=> new Statistic(x.Key, x.Count()) );


    return stats.OrderBy(x=>x.Date);