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?

Why aren't you using relationships in EF this seams to be simple column table mappings? — Filip Cordas
– Filip Cordas, Commented Oct 23, 2017 at 13:37
What is the point of your JobTypes entity if you store the full code in your FinishedWork entity ? — Rafalon
– Rafalon, Commented Oct 23, 2017 at 13:38
@FilipCordas I understood what you mean, but before switch my logic lets focus on query please if I cant then ok we can discuss it — TyForHelpDude
– TyForHelpDude, Commented Oct 23, 2017 at 13:42
@Rafalon, there are many properties I just make it shorter to clearify things — TyForHelpDude
– TyForHelpDude, Commented Oct 23, 2017 at 13:43

ocuenca · Accepted Answer · 2017-10-23 14:14:40Z

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.

duongthaiha · Accepted Answer · 2017-10-23 13:45:14Z

0

You want can do a query with group by name and job type then pivot using Pivot extension to return what you wanted http://linqlib.codeplex.com/wikipage?title=Pivot&referringTitle=Home

answered Oct 23, 2017 at 13:45

duongthaiha

8551 gold badge6 silver badges17 bronze badges

Collectives™ on Stack Overflow

build relational linq query among multiple tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related