Naveen's Weblog

Bridge to future

Left Join, Right Join Using LINQ

Posted by codingsense on March 8, 2009


Hi Friends,

In this post let us see how we can handle Left Join and Right Join when using LINQ. There are no keywords defined in C#, we have to use DefaultIfEmpty() function to get the desired result.
Let us see how we can achieve it.

To make you understand better I use a Employee -> Department realation to explain.

First we shall create two classes namely Employee and Department

class Employee    
{
public string Name { get; set; }
public int ID { get; set; }
public int DeptID { get; set; }
}

class Department    
{
public int ID { get; set; }
public string Name { get; set; }
}

Lets create some objects of both the classes and fill some dummy data in it.

Employee emp1 = new Employee() { ID = 1, Name = "A", DeptID = 1};
Employee emp2 = new Employee() { ID = 2, Name = "B", DeptID = 1};
Employee emp3 = new Employee() { ID = 3, Name = "C", DeptID = 1 };
Employee emp4 = new Employee() { ID = 4, Name = "D", DeptID = 2 };
Employee emp5 = new Employee() { ID = 5, Name = "E", DeptID = 2 };
Employee emp6 = new Employee() { ID = 6, Name = "F", DeptID = 2 };
Employee emp7 = new Employee() { ID = 7, Name = "G", DeptID = 6 };
Employee emp8 = new Employee() { ID = 8, Name = "H", DeptID = 3 };
Employee emp9 = new Employee() { ID = 9, Name = "I", DeptID = 3 };
Employee emp10 = new Employee() { ID = 10, Name = "J", DeptID = 7};
Employee emp11 = new Employee() { ID = 11, Name = "K", DeptID = 7};
Employee emp12 = new Employee() { ID = 12, Name = "L", DeptID = 5};

Department Dept1 = new Department() { ID = 1, Name = "Development"};
Department Dept2 = new Department() { ID = 2, Name = "Testing"};
Department Dept3 = new Department() { ID = 3, Name = "Marketing"};
Department Dept4 = new Department() { ID = 4, Name = "Support"};

List<Employee> ListOfEmployees = new List<Employee>();
ListOfEmployees.AddRange((new Employee[] { emp1, emp2, emp3, emp4, emp5, emp6, emp7,
emp8, emp9, emp10, emp11, emp12 }));

List<Department> ListOfDepartment = new List<Department>();
ListOfDepartment.AddRange( new Department[]{ Dept1,Dept2,Dept3,Dept4});

So we finish loading the objects into ListOfEmployees and ListOfDepartments, using this lists we shall see how we can join them to get the results.
First let us see what would be the query in SQL if we had the same structure in our tables.
For Left join and right join we would have used the query

--Left Join in SQL
select Emp.Name, Dept.Name from Employee Emp left join Department Dept on
Emp.DeptID = Dept.ID

--Right Join In SQL
select Emp.Name, Dept.Name from Employee Emp right join Department Dept on
Emp.DeptID = Dept.ID

Using LINQ, Left Join can be acheived as follows

var LeftJoin = from emp in ListOfEmployees
join dept in ListOfDepartment
on emp.DeptID equals dept.ID into JoinedEmpDept 
from dept in JoinedEmpDept.DefaultIfEmpty()
select new                          
{
EmployeeName = emp.Name,
DepartmentName = dept != null ? dept.Name : null                          
};

And for Right Join there is no pretty difference, we just need to reverse the joining in first 2 lines. Here it follows

var RightJoin = from dept in ListOfDepartment
join employee in ListOfEmployees
on dept.ID equals employee.DeptID into joinDeptEmp
from employee in joinDeptEmp.DefaultIfEmpty()
select new                            
{
EmployeeName = employee != null ? employee.Name : null,
DepartmentName = dept.Name
};

Do you believe it will work ??? lets check it by displaying,

Console.WriteLine(string.Join("n" , LeftJoin.Select(emp => " Employee Name = " +
emp.EmployeeName + ", Department Name = " + emp.DepartmentName).ToArray<string>()));

Console.WriteLine(string.Join("n", RightJoin.Select(emp => " Employee Name = " +
emp.EmployeeName + ", Department Name = " + emp.DepartmentName).ToArray<string>()));

Output of Left Join in LINQ

Output of Left Join in LINQ

Output of Right Join in LINQ

Output of Right Join in LINQ

Great, it has worked. So friends in this way you can handle Left and Right Joins in LINQ, hope you understood the sample.

Next -> Multiple List Left Join

Happy Learning :)

About these ads

24 Responses to “Left Join, Right Join Using LINQ”

  1. Kantimaya said

    Hi,
    I have two lists on which i am applying linq to get my result as ID,Particulars,amount and date in the result.
    Now i want to add two more records,one in the begining as ID,’My Particulars’,amount,’My date’ and same one in the last of the result.Is there any solution for this?

    • codingsense said

      Hi Kanti,

      Yes you can do it. Using Union will help you achieve the result.

      If you have 3 lists, List1 , list2 and list3 and if you want list2 in first and list3 in last then use,

      var Result = List2.Union(List1).Union(List3)

      The result will have what is required.

      Thanks,
      Naveen Prabhu

  2. Sheir said

    Hi,
    I am trying to do a Left Join following your example on my typed dataset have a couple problems I hope you can help me solve.

    1) I need to do 3 left join operators between various tables

    2) In your Select New clause for the child record which might be null, you do something like
    DepartmentName = dept != null ? dept.Name : null

    How do I code that if the child property is not a string but an int?
    As an example:
    DepartmentID = dept != null ? dept.Id : null

    does not work.

    Thanks for any help.
    Sheir

    • codingsense said

      Hi Sheir,

      In my example i wanted the department name as output so i m taking “null” if no department exists, “null” is the default value of the department name i.e string.

      Similarly if you want int output then
      1) you can either use 0 in place of null as default value.
      2) or you can make the ID field in the department class and nullable int and use the same code.
      public int? ID { get; set; }

      And for multiple joining, here is the syntax
      FROM (A LEFT JOIN B ON A.id = B.id)
      LEFT JOIN C ON A.id = C.id;

      Hope your doubts are clear, if not feel free to ask :).
      Thanks,
      Naveen Prabhu

  3. Sheir said

    Thanks for the reply.

    I had figured out the non-string null issue.
    As an example:

    select new
    {
    RegisterDate = (employee != null) ? employee.RegisterDate : (DateTime?) null,
    EmployeeId = (employee != null) ? employee.EmployeeId : (int?) null
    };

    But i do not think that there is a “left join” in the Linq syntax so am not sure your multiple joining example would work.

    What I ended up doing was using several “var” variables to get the multiple left join done; using the previous “var” in the new “var”.
    As an example:

    var lnqLeftJoin1 =
    from aRow in TableA
    join bRow in TableB
    on aRow.Bid equals bRow.Bid
    into LeftJoinGroup
    from grpRow in LeftJoinGroup.DefaultIfEmpty()
    select new
    {
    aRow.Aid,
    aRow.Bid,
    aRow.Cid,
    BAddressId = (grpRow != null) ?
    grpRow.AddressId
    : (int?)null
    };

    var lnqLeftJoin2 =
    from ljRow in lnqLeftJoin1
    join cRow in TableC
    on ljRow.Cid equals cRow.Cid
    into LeftJoinGroup
    from grpRow in LeftJoinGroup.DefaultIfEmpty()
    select new
    {
    ljRow.Aid,
    ljRow.Bid,
    ljRow.Cid,
    ljRow.BAddressId,
    OrderId = (grpRow != null) ?
    grpRow.OrderId
    : (int?)null,
    OrderDate = (grpRow != null) ?
    grpRow.OrderDate
    : (DateTime?)null,
    };

    That seem to work but am not sure if its the best way to handle multiple left join scenario.

    • codingsense said

      Hi Sheir,

      Yes, the similar type of implementation is used for Multiple List joining. First join the 2 list and the output of it is joined with the 3ed one and so on. Here is one post for your reference
      Multiple List Join Using LINQ

      Thanks,
      Naveen Prabhu

  4. Dan said

    Naveen,

    I have a LINQ statement working with multiple joins. It returns the detailed results. My problem lies after the statement has run. I am trying to run a “.Sum” on the query results and it fails stating – Overload resolution failed because not accessible ‘SUM’ accepts this number of arguments.

    What am I missing?

    Thanks,
    Dan

    Dim Query = From TC In objTimeWorkedLinq.SA_Timeclocks
    Group Join JC In objTimeWorkedLinq.Job_Cds
    On TC.Job_Code Equals JC.Job_Code
    Into loj1 = Group
    From TC1 In loj1.DefaultIfEmpty
    Group Join JCT In objTimeWorkedLinq.Job_Type_Cds
    On TC1.Job_Type_Code Equals JCT.Job_Type_Code
    Into loj2 = Group
    From TC2 In loj2.DefaultIfEmpty
    Where (p_intAssociateUID = -1
    OrElse TC.Associate_UID = p_intAssociateUID)
    And (p_intStoreUID = -1
    OrElse TC.Store_UID = p_intStoreUID)
    And TC.Date_Time >= p_datStartDateTime
    And TC.Date_Time <= p_datEndDateTime
    And (TC2.Include_Hours_Ind = "Y" Or TC2.Include_Hours_Ind Is Nothing)
    Select New With {.Productive_Hours_Qty = TC.Productive_Hours_Qty}

    totalHours = Query.SUM

  5. Dan said

    Naveen,

    I have found ananswer. The “Select” line should be
    “Select Total_Hours = TC.Productive_Hours_Qty”. If there is a better way please let me know.

    Thanks,
    Dan

  6. nice artilce , by this i am able to know the concept indept , i have one query can you convet to linq if possinel

    SELECT C.CameraID,C.Title,C.ActiveFlag,C.Comments,C.RegisteredOn,(SELECT Max(ReportedOn) From FotoUHeartBeat Where CameraID=C.CameraID) as Reported FROM FotoUCamera as C
    WHERE SiteID=@siteID

    • codingsense said

      Hi Manju,

      The query would be as follows

      var output = from C in ListOfFotoUCamera
      where C.SiteID == siteID
      select new
      {
      CameraID = C.CameraID,
      Title = C.Title,
      ActiveFlag = C.ActiveFlag,
      RegisteredOn = C.RegisteredOn,
      Reported = ListOfFotoUHeartBeat.Where(item => item.CameraID == C.CameraID).Max(item => item.ReportedOn),
      };

      The list that I have used will be

      List<FotoUCamera> ListOfFotoUCamera = new List<FotoUCamera>();
      //Add appropriate entries in the list

      List<FotoUHeartBeat> ListOfFotoUHeartBeat = new List<FotoUHeartBeat>();
      //Add appropriate entries in the list

      and the classes that will be used are

      class FotoUCamera
      {
      public int CameraID { get; set; }
      public int Title { get; set; }
      public int ActiveFlag { get; set; }
      public string Comments { get; set; }
      public DateTime RegisteredOn { get; set; }
      public int SiteID { get; set; }
      }

      class FotoUHeartBeat
      {
      public int CameraID { get; set; }
      public DateTime ReportedOn { get; set; }
      }

      Thanks,
      Codingsense

      • Ratheesh said

        Hi,
        Is it possible to convert this SQL query to Linq?

        SELECT TripLeg.FromTripEventId,TripLeg.ToTripEventId,TripLegITASlice.ITASliceNumber, TripLegITASlice.ITARequestID,
        vwEventAirportTimes.Subject AS MeetingNameFrom, EventAirportTimes_1.Subject AS MeetingNameTo, vwEventAirportTimes.AirportId AS Origin,
        vwEventAirportTimes_1.AirportId AS Destination, vwEventAirportTimes.DepartAirportAfter, EventAirportTimes_1.ArriveAirportBy,
        vwEventAirportTimes_1.MileageCost AS ArriveMileageCost, vwEventAirportTimes_1.ArriveTimeCost, vwEventAirportTimes_1.ArriveTotalCost,
        vwEventAirportTimes_1.ArriveTotalTime, vwEventAirportTimes.MileageCost AS DepartMileageCost, vwEventAirportTimes.DepartTimeCost,
        vwEventAirportTimes.DepartTotalCost, vwEventAirportTimes.DepartTotalTime
        FROM vwEventAirportTimes AS vwEventAirportTimes_1 INNER JOIN
        vwEventAirportTimes INNER JOIN
        TripLegITASlice INNER JOIN
        TripLeg ON TripLegITASlice.TripLegID = TripLeg.TripLegId ON vwEventAirportTimes.TripEventId = TripLeg.FromTripEventId ON
        EventAirportTimes_1.TripEventId = TripLeg.ToTripEventId

  7. Manjunath said

    Thanku you very much for you help

  8. Pag said

    Why doesn’t this produce the same left join:


    ListOfEmployees
    .Join (
    ListOfDepartment.DefaultIfEmpty(null),
    e => e.DeptID,
    d => d.ID,
    ( e, d ) => new { EmployeeName = e.Name, DepartmentName = d != null ? d.Name : null } )

    • codingsense said

      Hi,

      The query you wrote is not for a Left Join, it will do the inner join where in both the list there has to be a match.

      The following query will do the inner join

      var InnerJoin = from emp in ListOfEmployees
      from dept in ListOfDepartment
      where emp.DeptID == dept.ID
      select new
      {
      EmployeeName = emp.Name,
      DepartmentName = dept.Name
      };

      Here we don’t use join keyword. we will iterate through each list and then where there is a match we display. See the difference in query in the post and the above mentioned.

      Thanks,
      Codingsense

  9. Axlrod said

    Thaks,
    Great and very simple solution

  10. siva said

    Hi
    i am very much new to linq
    now i am able to join 2 table using left join
    my case is to join 3 and more tables

    Warm Regrads

  11. ramki said

    Hi,

    Can you convert below sql query into LINQ for me please

    ***********************************************************

    select * from Product_Model pm

    left join eCatalog_ITSC_Extension catExtn

    on pm.catalog_id=catExtn.catalog_id

    where pm.product_model_status_code = ‘ct’

    AND pm.Catalog_ID=’catalog_id’

    Thanks in advance,

    Rama Krishna

  12. séddik said

    Gread help, thanx

  13. Sindhu V said

    thanks a lot…good article

  14. jamad said

    Hi,

    I am new to LinQ but I have hard time understanding how your query works. In both left and right joins you are actually using simple join to get the result set into temp var joinDeptEmp

    I believe the contents of joinDeptEmp will be (correct me if I am wrong) following since it simple join

    Emp=A,Dept=Development
    Emp=B,Dept=Development
    Emp=C,Dept=Development
    Emp=D,Dept=Testing
    Emp=E,Dept=Testing
    Emp=F,Dept=Testing
    Emp=H,Dept=Marketing
    Emp=I,Dept=Marketing

    Now when you cross join it with either Department (for left join) or Employee (for right join) that’s is where I lost how this left or right join result is produced. Is there any way to see what is temp var joinDeptEmp holds. Thanks for your help.

    • codingsense said

      Hi Jamad,

      Sorry for the delay, the example demonstrated is for left join and right join as in SQL. For cross join find the linq query below.

      var LeftJoin = from emp in ListOfEmployees
      from dept in ListOfDepartment
      where emp.DeptID == dept.ID
      select new
      {
      EmployeeName = emp.Name,
      DepartmentName = dept != null ? dept.Name : null
      };

      Hope this query will help you to find our the difference between the two.

      Thanks,
      Naveen

    • codingsense said

      Hi Jamad,

      Sorry for the delay, the example demonstrated is for left join and right join as in SQL. For cross join or equi join find the linq query below.

      var crossJoin = from emp in ListOfEmployees
      from dept in ListOfDepartment
      where emp.DeptID == dept.ID
      select new
      {
      EmployeeName = emp.Name,
      DepartmentName = dept.Name
      };

      Hope this query will help you to find our the difference between the two.

      Thanks,
      Naveen

  15. Rashmi said

    Heyyyyyyyyyyyy Naveena.. its Rashmi.. I was searching for left joins in linq and here am … good to c u…………………….And ur blog helped to solve my pbm… God bless u..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: