Foreign Key Object Missing in LINQ GroupBy?

Question

I am using GroupBy in my LINQ queries. My query is working fine, except my foreign key objects are missing. Then, I tried to add Include in my query. Following is my code:

public ActionResult GetEmployees()
{
     var Emloyees = db.Employees.AsQueryable();
     Emloyees.GroupBy(e=> new {e.JobTitleId, e.GenderId})
             .Select(tr => new MyObject
                 SalaryTotal = tr.Sum(r=>r.Salary)
              }).Include(tr=>tr.JobTitle).Include(tr=>tr.Gender).ToList();
}

I am getting this exception:

The result type of the query is neither an EntityType nor a CollectionType with an entity element type. An Include path can only be specified for a query with one of these result types.

I tried to add it before GroupBy and directly in db.Employees.AsQueryable(), but nothing worked. What am I doing wrong?

Answer 1

The problem is that you are doing a projection with the .Select(...), after which your includes cannot be resolved. The result of that query will be a list MyObject, which does not work for includes that are actually on Employee.

So Try it like this:

Emloyees
    .GroupBy(e=> new {e.JobTitleId, e.GenderId})
    .Select(tr => new MyObject {
        SalaryTotal = tr.Sum(r=>r.Salary),
        JobTitle = tr.FirstOrDefault().JobTitle,
        Gender = tr.FirstOrDefault().Gender
    }).ToList();

You will have to extend MyObject with 2 additional properties, but the result of your query will be what you want (I assume).