build relational linq query among multiple tables

Question

I will make it as clear as I can; My first table contains types of jobs;

public class JobTypes: Entity
    {
        public string Code { get; set; }
    }

And here is my second entity contains all jobs;

public class FinishedWork: Entity
    {
        public string JobTypeCode { get; set; }
        public int DepartmentId { get; set; }
        public int EmployeeId { get; set; }
    }

and employees and Department:

public class Employee: Entity
    {
        public string Name { get; set; }
    }
public class Department: Entity
    {
        public string DepartmentName { get; set; }
    }

Query result should answer; "Number of finished work for each job type of each employee" For example, the result should look like:

Employee JobType1 JobType2 ... JobType122  DepartmentName (Yes, there is 122 job type and result should tell how many work done by alice each job)
Alice    2         0       ... 0,          AccountManagement
...

and my query to achieve it;

var mdl = (from m in FinishedWork
                           join t in JobTypes m.JobTypeCode equals t.Code
                           join d in Department on m.DepartmentId equals d.ID
                           join e in Employee on m.EmployeeId equals e.ID
                           group m by new { e.ID, e.Name, m.JobTypeCode } into grp
                           select new ResultModel
                           {
                             ...

I couldnt find proper solution here, How can I get counts for each job types into this result model?

Answer 1

I think if you use FinishedWork entity as start point and use the navigation properties in your model instead your query could be improved:

var query =context.FinishedWork.GroupBy(fw=>new{fw.Employee.Name,fw.Department.DepartmentName})
                               .Select(g=>g.GroupBy(fw=>fw.JobTypeCode )
                                           .Select(g1=>new {g.Key.Name,
                                                            g.Key.DepartmentName,
                                                            g1.Key,
                                                            g1.Count()}));

The issue is I don't know how add fields in the anonymous type for each inner groups (that represent job types) without knowing the amount of group since the beginning, to do something like:

var query =context.FinishedWork.GroupBy(fw=>new{fw.Employee.Name,fw.Department.DepartmentName})
                               .Select(g=>new {g.Key.Name,
                                               g.Key.DepartmentName,
                                               JobType1=g.Count(fw=>fw.JobTypeCode==code1),
                                               JobType2=g.Count(fw=>fw.JobTypeCode==code2),
                                               ...
                                              });

And I think you don't want to do that, if I was you I would use the first solution, or another one better that I hopefully expect that exist.