How to Create the Left Join by using the group by data

Question

I have three Table One is "Allowance " ,"Balance" and "TimeoffRequests" in these three table common columns are EmployeeId and TimeoffTypeId, Now i need to get the requested hours of one leave type by grouping thier timeoffTypeId and EmployeeId from the table "TimeoffRequests" , and got the "TimeOffHours". for the i wrote the code like

var query = (from tr in TimeOffRequests
             where tr.EmployeeID == 9
             group tr by new { tr.EmployeeID, tr.TimeOffTypeID } into res
             select new
             {
                 EmployeeID = res.Key.EmployeeID,
                 TimeOffTypeID = res.Key.TimeOffTypeID,
                 TotalHours = res.Sum(x => x.TimeOffHours)
             }).AsEnumerable();

Now I need to join these results with the first table and have to get the all the employees, and timeoffTypes from the UserAllowance and corresponding TimeoffHours from the grouped table. for getting left joined query i wrote like below.

var requestResult = (from UA in UserAllowances
                     join UB in UserBalances on UA.EmployeeID equals UB.EmployeeID
                     where UA.TimeOffTypeID == UB.TimeOffTypeID && UA.EmployeeID == 9
                      && UA.TimeOffType.IsDeductableType == true      // LeftJoin

                     join rest in query on UA.EmployeeID equals rest.EmployeeID into penidngRequst
                     from penReq in penidngRequst.DefaultIfEmpty()
                     where penReq.TimeOffTypeID == UA.TimeOffTypeID

                     select new EmployeeTimeOffBalanceModel
                     {
                         TimeOffTypeID = UA.TimeOffTypeID != null ? UA.TimeOffTypeID : 0,
                         YearlyAllowanceHrs = (UA.YearlyAllowanceHrs != null) ? UA.YearlyAllowanceHrs : 0,
                         BalanceHours = UB.BalanceHrs != null ? UB.BalanceHrs : 0,
                         PendingHours = (decimal)((penReq != null) ? (penReq.TotalHours) : 0),
                         EmployeeID = UA != null ? UA.EmployeeID : 0,


                     }).ToList().Distinct();

It is giving only timeOFfType containing in grouped data,even though I wrote leftjoin for the query using the "into" and DefaultIfEmpty() keywords. the results becomes as like:

and by using the "linqPad" editor i found that it is applying the Cross or Outer Join instead of "left join" what will be the reason.

If I remove this line of code " where penReq.TimeOffTypeID == UA.TimeOffTypeID" this showing all the timeoffTypes with cross join with repeatation like

How can I achieve left join with tables with Grouped data and showing null values if timeofftypes didn't having the any request?

Answer 1

You might want to move the where clause into the the on equals clause as shown below

join rest in query on new { UA.EmployeeID, UA.TimeOffTypeID } equals new { rest.EmployeeID, rest.TimeOffTypeID } into penidngRequst
from penReq in penidngRequst.DefaultIfEmpty()