Entity Framework object limitations in aggregate LINQ query

Question

I have a rather complicated query that I'd like to have return some particular types. Mostly concerning date / time calculations or string values, the Entity Framework seems to be spitting the dummy when I try anything further than a System.DateTime type:

The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

The query in question is:

    // Here comes the aggregate query.
    object summary = from te in ctx.TimeEntries
                     where te.StartTime >= startofweek
                     && te.UserName == User.Identity.Name
                     group te by te.StartTime.Day into Groups
                     select new
                     {
                         Key = Groups.Key,
                         Count = Groups.Count(),
                         //Total = Groups.Sum(n => (double)((n.EndTime ?? DateTime.Now) - n.StartTime).TotalMinutes / 60),
                         Tasks = from t in Groups
                                 orderby t.StartTime descending
                                 select new
                                 {
                                     Name = t.Task.TaskName,
                                     Project = t.Task.Batch.Project.ProjectName,
                                     Batch = t.Task.Batch.BatchName,
                                     //Minutes = ((t.EndTime ?? DateTime.Now) - t.StartTime).Minutes,
                                     Start = t.StartTime.Date,
                                     Stop = t.EndTime,
                                     Description = t.Notes,
                                     Breaks = t.BreakFor ?? 0
                                 }
                     };

In the query above, the property "Start" will fail with the aforementioned error. However, if I simply have "Start = t.StartTime" everything will be fine. Similarly, the commented out value for "Minutes" will also cause problems.

Suggestions most welcome!

Answer 1

You can only use supported members in L2E queries, and Date isn't one of those.

One workaround is to break your single L2E query into one L2E query followed by one LINQ to Objects query:

var q = from e in Context.Entities
        select new
        {
            Id = e.Id,
            DateTime = e.DateTime
        };

var r = from e in q.AsEnumerable()
        select new
        {
            Id = e.Id,
            Date = e.DateTime.Date
        };