There are 0 to n departments in my company, 0 to n offices in 1 department, and 0 to n emplyees in 1 office.Now I need a query using linq to list emplyees's average age by department, if nobody in a department then default average is 0. code is below:
DataContext ctx = new DataContext();
var q0 = from d in ctx.Departments
join o in ctx.Offices on d.Id equals o.DepartmentId
join e in ctx.Employees on o.Id equals e.OfficeId
group e by d into de
select new {
DepartmentId = de.Key.Id,
AverageAge = de.Count() == 0 ? 0 : de.Average(e => e.Age),
};
var q1 = from d in ctx.Departments
join de in q0 on d.Id equals de.DepartmentId into des
from de in des.DefaultIfEmpty()
select new
{
DepartmentName = d.Name,
AverageAge = de == null ? 0 : de.AverageAge
};
var result = q1.ToList();
foreach (var item in result)
{
Console.WriteLine("{0}-{1}", item.DepartmentName, item.AverageAge);
}
ctx.Dispose();
But how to combine q0 and q1 to one query?
Were you meaning something along the lines of:
var newQ2 = from d in ctx.Departments
outer left join o in ctx.Offices on d.Id equals o.DepartmentId
outer left join e in ctx.Employees on o.Id equals e.OfficeId
group e by d into de
select new {
DepartmentId = de.Key.Id,
AverageAge = de.Count() == 0 ? 0 : de.Average(e => e.Age),
};
Changed to:
var newQ2 = from d in ctx.Departments
join o in ctx.Offices on d.Id equals o.DepartmentId
join e in ctx.Employees on o.Id equals e.OfficeId
group e by d into de.DefaultIfEmpty()
select new {
DepartmentId = de.Key.Id,
DepartdentName = select d.Name from d where d.id = de.Key.Id,
AverageAge = de.Count() == 0 ? 0 : de.Average(e => e.Age),
};
Addendum: I would use a sub-select to match up the extra name, not knowing your db layout I have improvised from your code, but you could make it more efficient and have a multipart join based on sub-selects as well. Sorry I cant test this code out at work, I can approximate fairly well, but would need some more info on where your department names are located if you need a more detailed answer:) I have changed the outer left joins back to joins, sorry I forgot in C# with linq you can use DefaultIfEmpty() to cause outer left join behaviour in code.
An outer left join will return nulls where there are no corresponding values, but will allow returns on any parts that do have a corresponding value. Join however will not return any null entries which I suspect is why you had the two queries?
The only caveat on the query I have presented is that you will need to infill any values you require before you use them if they are nulls, for example DepartmentId will need some logic to populate it in case DE is null.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With