CodeReaper CodeReaper - 2 months ago 21
C# Question

LINQ aggregate multiple tables

I have the following database. A list of companies. Each company has multiple employees and multiple contractors.

dbo.Companies (CompanyId, Name)
dbo.Employees (Id, CompanyId, Name ...)
dbo.Contractors(Id, CompanyId, Name...)


I want to get output like so

CompanyName #Employees #Contractors
abc 0 10
xyz 25 999


I am trying to avoid doing 2 queries, one to get contractors and one to get employees and then merging them. Is there a way to get it done in one go?

n.b. i have

class CompanySummary{
string Name {get; set;}
int EmpCount {get; set;}
int ConCount {get; set;}
}


so I can use a collection of this type as result

Answer

If you have defined navigation properties (and if you haven't, may be it's a good time to do that), the query should be quite simple:

var query = from c in db.Companies
            select new CompanySummary
            {
                Name = c.Name,
                EmpCount = c.Employees.Count(),
                ConCount = c.Contractors.Count(),
            };

Of course you can do that manually, but the above is the preferred way with EF:

var query = from c in db.Companies
            select new CompanySummary
            {
                Name = c.Name,
                EmpCount = db.Employees.Count(e => e.CompanyId == c.Id),
                ConCount = db.Contractors.Count(cc => cc.CompanyId == c.Id),
            };

In both cases you'll get a single SQL query.

Comments