0

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.

Roger Oliveira
  • 1,589
  • 1
  • 27
  • 55
  • @antlersoft I tried but I couldn't understand that one, could you help me with the solution, would apreciate :) – Roger Oliveira Oct 07 '15 at 22:58
  • @antlersoft I am struggling here to make it work, I tried 20 examples, my case is slightly different, I have group by two fields, this website is suposed to help specific problems also. thanks – Roger Oliveira Oct 07 '15 at 23:36
  • 1
    Well, you should have linked it, and told exactly what you didn't understand about it. You can still do that! (Even if it would already have been closed, that wouldn't be the end unless you let it be the end.) Aside:: I probably won't be able to help you with an answer, not my bailwick. – Deduplicator Oct 07 '15 at 23:54
  • @Deduplicator I appreciate your attention, you are a legend, and I have great news I made it work!! Is it something useful to post the solution in this post? Thanks – Roger Oliveira Oct 08 '15 at 00:19
  • 1
    Great news. Well, it's your decision whether posting a new answer to http://stackoverflow.com/questions/3404975/left-outer-join-in-linq, polishing (one or more) answers there, or posting a new answer here would be the better contribution to SO. I just don't know. – Deduplicator Oct 08 '15 at 02:52

0 Answers0