Click here to Skip to main content
15,074,483 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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


C#
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();



resultant grouped data
Now i need to join these results with the first table and have to get the all the employees, and timeoffTypes from the UserAllowance (which contains 6 records) and corresponding "TimeoffHours" from the grouped table. for getting left joined query i wrote like below.

C#
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(instead of 6 records it's giving 3 records),even though i wrote leftjoin for the query using the "into" and "DefaultIfEmpty()" keywords. the results becomes as like:


The result as[^]

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 crossjoin with repeatation like


The reultant data[^]

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

Thank you.
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900