In Visual Studio 2013
, C# 4.5
, I have these classes:
Entities:
public class KpiLabourHoursByTeamRole
{
public DateTime Date { get; set; }
public int TeamId { get; set; }
public int RoleId { get; set; }
public double TotalHours { get; set; }
public double TotalCost { get; set; }
}
public class TeamRole
{
public int Id { get; set;}
public int RoleId { get; set;}
public int TeamId { get; set;}
public int? LabourCostCenterId { get; set; }
}
public class KpiLabourHoursByCostCenter
{
public int? LabourCostCenterId { get; set; }
public double TotalHours { get; set; }
public double TotalCost { get; set; }
}
Current Solution:
Currently, I have the solution below working perfectly as a Inner Join
using Linq
:
kpiLabourHours
is a object of the type: KpiLabourHoursByTeamRole
kpiLabourHoursByCostCenter
is a object of the type: KpiLabourHoursByCostCenter
var kpiLabourHoursByCostCenter = from k in kpiLabourHours
join tr in teamRoles on new {k.TeamId, k.RoleId} equals new {tr.TeamId, tr.RoleId}
group k by new { tr.LabourCostCenterId }
into grp
select new KpiLabourHoursByCostCenter
{
LabourCostCenterId = grp.Key.LabourCostCenterId,
TotalHours = grp.Sum(k => k.TotalHours),
TotalCost = grp.Sum(k => k.TotalCost),
};
Equivalent in Sql:
The code above is represented by the equivalent Sql
:
SELECT tr.LabourCostCenterId, sum(k.TotalHours), sum(k.TotalCost)
FROM kpiLabourHours k
INNER JOIN TeamRoles tr on tr.TeamId = k.TeamId and tr.RoleId = k.RoleId
GROUP tr.LabourCostCenterId
My problem:
The problem is that it is hidding the results where there is no matching TeamId
and RoleId
, so I need to replace this code by a kind of Left Outer Join
, such as:
SELECT tr.LabourCostCenterId, sum(k.TotalHours), sum(k.TotalCost)
FROM kpiLabourHours k
LEFT OUTER JOIN TeamRoles tr on tr.TeamId = k.TeamId and tr.RoleId = k.RoleId
GROUP tr.LabourCostCenterId
In a nutshell, I need to return LabourCostCenter NULL
or 0
if there is no matching TeamId
and RoleId
with the totals.