Getting Entity Framework to eager load on Group By

Question

I know that changing the shape of a query causes Entity Framework to ignore the include calls but is there a way I can get it to load the sub properties when I do a select many and a group by. In the following example I want to notify all the employees who have a job booked in a certain time period. Calling .ToArray() after the where only hits the database once but I am doing the SelectMany and GroupBy in memory. Is there a way I can get the SelectMany and the GroupBy to happen on the SQL server and still include the ServiceType and Ship and the Employee details?

I am looking for a way to make one SQL call to the database and end up with a list of Employees who have a job in the time period and the jobs they are assigned to.

var employeeJobs = DataContext.Jobs.
    Include("ServiceType").
    Include("Ship").
    Include("JobEmployees.Employee").
    Where(j => j.Start >= now && j.Start <= finish).
    OrderBy(j => j.Start).
    ToArray().
    SelectMany(j => j.JobEmployees, (j, je) => new { 
        Job = j, 
        Employee = je.Employee 
    }).GroupBy(j => j.Employee);

_I am doing the SelectMany and GroupBy in memory_. How do you know? The shape of the query in EF can be elusive. — Gert Arnold, Sep 18 '12 at 09:15
If you want to end up with a list of employees and their jobs, I'd suggest doing the query the other way around: employeeJobs = from DataContext.Employees e where e.Any(... has job criteria... ) select e. I'm not capable of writing the LINQ query for you without some intellisense help, but you get the idea? — GTG, Sep 18 '12 at 09:45
@GertArnold The call to `ToArray` materializes the results, the `SelectMany` and `GroupBy` come *after* the call to `ToArray`. — casperOne, Sep 18 '12 at 16:00
If I remove the ToArray() before the SelectMany then I loose the Includes hence the point of my question. When I do a foreach loop on the result I get an exception that a data reader is already open for the command as the sub properties are not loaded. I don't want to turn on multiple readers as it would be lazy loading the sub properties and hitting the database on each iteration. I would rather do the SelectMany and GroupBy in memory than multiple trips to the database. — Adrian Brand, Sep 18 '12 at 23:58
More conclusive discussion on the same topic here: http://stackoverflow.com/questions/5343536/linq-ef-eager-loading-and-group-by-issues — Brian Sweeney, Dec 09 '14 at 22:00

score 1 · Answer 1 · answered Feb 19 '15 at 11:44

The following should work:

var q = from e in DataContext.Employees
  where e.Job.Start > ....
  order by e.Job.Start
  select new {Employee = e, Job = e.Job, Ship = e.Job.Ship, ServiceType = e.Job.ServiceType}; // No db hit yet.

var l = q.GroupBy(item=>item.Employee) // no db hit yet.
    .ToList(); // This one causes a db hit.

score 0 · Answer 2 · answered Sep 19 '12 at 11:27

0

why don't you create a view and then reference this from the EF? , this also has the added benefit of the database server doing the work, rather than the app server.

answered Sep 19 '12 at 11:27

chris

551
1
13
32

The whole point of EF is that the work **is** being done on the database server. Thus your answer makes no sense. – Kirk Woll Jun 24 '14 at 15:15

score -1 · Answer 3 · answered Mar 18 '14 at 21:16

-1

Trying move the Include()'s to the very end after your groupby.

answered Mar 18 '14 at 21:16

Jason Parker

4,960
4
41
52

Include is defined on ObjectQuery and not for IQueryable which is the return type of GroupBy. Don't think this works :( – Brian Sweeney Dec 09 '14 at 21:54

Getting Entity Framework to eager load on Group By

3 Answers3