none
Fetching the data by quering more tables using the Left join with grouped data in linq RRS feed

  • Question

  • Hi,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 time-off TypeId 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 (which contains 6 records) 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      // Trying to achieve 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:

    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

    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.


    V G S Naidu


    • Edited by V G S Naidu Tuesday, February 4, 2014 4:39 AM
    Monday, February 3, 2014 5:31 AM

Answers

  • Hello,

    Making a similar demo with yours and it can show all the records from the UA and show null record if no matches found it in "TimeOffRequests" which is grouped based on EmployeeId.

    var query = (from tr in db.TimeOffRequests
    
                                 where tr.EmployeeID == 1
    
                                 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();
    
                    var requestResult = (from UA in db.Allowances
    
                                         join UB in db.Balances on UA.EmployeeID equals UB.EmployeeID
    
                                         where UA.TimeOffTypeID == UB.TimeOffTypeID
    
                                         join rest in query on UA.EmployeeID equals rest.EmployeeID into penidngRequst
    
                                         from penReq in penidngRequst.DefaultIfEmpty()
    
                                         where penReq.TimeOffTypeID == UA.TimeOffTypeID || penReq.TimeOffTypeID == null
    
                                         select new
    
                                         {
    
                                             UA,
    
                                             UB,
    
                                             penReq
    
                                         }).ToList().Distinct();
    

    The result:


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, February 5, 2014 8:57 AM
    Moderator

All replies

  • Hello,

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

    It actually is what you have done: using the "into" and "DefaultIfEmpty()" keywords like below:

    var result = from order in db.Orders
    
                                 join od in db.OrderDetails on order.OrderID equals od.OrderID into ood
    
                                 from orderod in ood.DefaultIfEmpty()
    
                                 select new { OrderID = order.OrderID, OrderDetailName = orderod.OrderDetailName };
    

    It will return null value:

    And if I add a where clause like:

    where order.OrderID == orderod.OrderID, 

    these null values then will never be shown again because  there are no null value in order table.

    >>if i remove this line of code " where penReq.TimeOffTypeID ==UA.TimeOffTypeID" this showing all the timeoffTypes

    So that is why when removing it, all the timeoffTypes will show or it will show three records, there are no null values in UA.

    If you want to know more, please check the link:

    http://msdn.microsoft.com/en-us/library/bb397895.aspx

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, February 4, 2014 2:47 AM
    Moderator
  • Hi Fred,

    Thank you for your reply, from your suggested the link, i hope i followed the same logic here to achieve the LeftouterJoin, Here what i need is, I need to show all the records from the UA, with matched records in "TimeOffRequests" which is grouped based on EmployeeId,if no matches found it has to show null records. for that i went for left join, but above query applying "CrossJoin" Instead of Left join.what will be the reason.


    V G S Naidu

    Tuesday, February 4, 2014 4:38 AM
  • Hello,

    Making a similar demo with yours and it can show all the records from the UA and show null record if no matches found it in "TimeOffRequests" which is grouped based on EmployeeId.

    var query = (from tr in db.TimeOffRequests
    
                                 where tr.EmployeeID == 1
    
                                 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();
    
                    var requestResult = (from UA in db.Allowances
    
                                         join UB in db.Balances on UA.EmployeeID equals UB.EmployeeID
    
                                         where UA.TimeOffTypeID == UB.TimeOffTypeID
    
                                         join rest in query on UA.EmployeeID equals rest.EmployeeID into penidngRequst
    
                                         from penReq in penidngRequst.DefaultIfEmpty()
    
                                         where penReq.TimeOffTypeID == UA.TimeOffTypeID || penReq.TimeOffTypeID == null
    
                                         select new
    
                                         {
    
                                             UA,
    
                                             UB,
    
                                             penReq
    
                                         }).ToList().Distinct();
    

    The result:


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, February 5, 2014 8:57 AM
    Moderator