Naveen's Weblog

Bridge to future

Multiple List Left Join in LINQ

Posted by codingsense on June 16, 2009


We had seen in earlier posts how to left join and right join 2 lists using LINQ. But what about multiple list joining is it in the same way or are there any challanges in the implementation.

Today we shall see how we can use left join on multiple list in a single query.

We will take a case of 3 classes Branch ( ID, Name) , Department(ID , Name, BranchID) and employees(ID,Name,DeptID)

Download Project – 2.9 Kb

If we would join the 3 similar tables in MSSQL then it would appear as

Select Emp.Name, Dept.Name, branch.Name from ((Employees as Emp left join 
Departments as Dept on Dept.ID = Emp.DeptID)left join 
Branches as branch on Dept.BranchID = branch.ID)

In the above query we can see that first we join Employees and department on Department ID and the output of that join is joined with branch on brachid. By following the same flow we can derive the same result using LINQ. The equivalent implementation in LINQ follows

var MultipleListJoin = from empdept in
                                   (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,
BranchID = dept != null ? dept.BranchID : 0,
DepartmentName = dept != null ? dept.Name : null                                    
})
join branch in ListOfBranch on
                               empdept.BranchID equals branch.ID into JoinedwithBranch
from branch in JoinedwithBranch.DefaultIfEmpty()
select new                               
{
EmpName = empdept.EmployeeName,
DeptName = empdept.DepartmentName,
BranchName = branch != null ? branch.Name : string.Empty
};

In the above first we derive the intermediate ouput by joining Employee and Department

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,
BranchID = dept != null ? dept.BranchID : 0,
DepartmentName = dept != null ? dept.Name : null}

and then use the intemediate output and join it with Branches to get the final result.

MultipleListLeftJoin

Hope you have got how to join multiple lists, for Right join follow the above implementataion in the reverse way.

Happy Learning :)

About these ads

11 Responses to “Multiple List Left Join in LINQ”

  1. bramoin said

    Hi,
    In your example for left join with multiple lists, it is easy when the class has a one or two members but if the class is large, how do you specify the whole thing rather than writing each one out. I am referring to the line below in the code segment that begins with <<<. (See below)

    var MultipleListJoin = from empdept in
    (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, <<< here you just want all the members in the emp, how do you specify that?
    BranchID = dept != null ? dept.BranchID : 0,
    DepartmentName = dept != null ? dept.Name : null
    })
    join branch in ListOfBranch on
    empdept.BranchID equals branch.ID into JoinedwithBranch
    from branch in JoinedwithBranch.DefaultIfEmpty()
    select new
    {
    EmpName = empdept.EmployeeName,
    DeptName = empdept.DepartmentName,
    BranchName = branch != null ? branch.Name : string.Empty
    };

    thank you,
    bramoin

    • codingsense said

      Hi bramoin

      Use the below query to get all the fields of emp.

      var MultipleListJoin = from empdept in
      (from emp in ListOfEmployees
      join dept in ListOfDepartment
      on emp.DeptID equals dept.ID into JoinedEmpDept
      from dept in JoinedEmpDept.DefaultIfEmpty()
      select new
      {
      emp,
      BranchID = dept != null ? dept.BranchID : 0,
      DepartmentName = dept != null ? dept.Name : null
      })
      join branch in ListOfBranch on
      empdept.BranchID equals branch.ID into JoinedwithBranch
      from branch in JoinedwithBranch.DefaultIfEmpty()
      select new
      {
      empdept,
      DeptName = empdept.DepartmentName,
      BranchName = branch != null ? branch.Name : string.Empty
      };

      Just replacing emp.name with emp will do. later if you need to access any fields of emp then use MultiList.empdept.emp.Name etc. For more clarification please get back.

      Thanks,
      Naveen Prabhu

  2. blake said

    just what I need, thanks bro

  3. Ratheesh said

    Hi,
    I had created List of classes for the following database tables but i cant covert the SQL to the LINQ

    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

    Can You help me to convert?

    • codingsense said

      Hi Ratheesh,

      Yes its possible to convert. I need the class structure to convert them. Can you send me the database structure so that i can convert them and give it.

      Or you here is a software that helps us to convert any kind of SQL queries to LINQ. Try it

      http://www.softpedia.com/get/Programming/Other-Programming-Files/Linqer.shtml

      Thanks,
      Codingsense

      • manoj said

        Hi,
        I am new to linq,Pls help me on the below sql query into Linq.My requirement is to retrieve all the records from the session table if either subjectid or testid can be null
        I tried with the linq ,but the output is not retreiving all the records from the session table for the passing student id.
        Thanks in advance

        SQL:
        select su.subjectid,su.subjectname,t.testid,t.testname,p.personname
        from session as se inner join tutor as tu on se.tutorid=tu.tutorid inner join person as p on tu.personid = p.personid left join subjects as su on se.subjectid = su.subjectid left join test as t on se.testid =t.testid where se.studentid =selectedStudID

        Linq:
        var getdata = (from se in studentdetails.session join tu in studentdetails.tutor on se.tutorid equals tu.tutorid join
        p in studentdetails.person on tu.personid=p.personid join
        su in studentdetails.subject on se.subjectid equals su.subjectid into sub
        join t in studentdetails on se.testid equals t.testid into tes
        from su in sub.defaultifempty()
        from t in tes.defaultifempty()
        select new
        {
        p.personname,
        subjectname = su.subjectname,
        testname = t.testname,
        se.sessionid
        });

  4. Shalu david said

    Very Helpful! Thanks!

    • manoj said

      Hi,
      I am new to linq,Pls help me on the below sql query into Linq.My requirement is to retrieve all the records from the session table if either subjectid or testid can be null
      I tried with the linq ,but the output is not retreiving all the records from the session table for the passing student id.
      Thanks in advance

      SQL:
      select su.subjectid,su.subjectname,t.testid,t.testname,p.personname
      from session as se inner join tutor as tu on se.tutorid=tu.tutorid inner join person as p on tu.personid = p.personid left join subjects as su on se.subjectid = su.subjectid left join test as t on se.testid =t.testid where se.studentid =selectedStudID

      Linq:
      var getdata = (from se in studentdetails.session join tu in studentdetails.tutor on se.tutorid equals tu.tutorid join
      p in studentdetails.person on tu.personid=p.personid join
      su in studentdetails.subject on se.subjectid equals su.subjectid into sub
      join t in studentdetails on se.testid equals t.testid into tes
      from su in sub.defaultifempty()
      from t in tes.defaultifempty()
      select new
      {
      p.personname,
      subjectname = su.subjectname,
      testname = t.testname,
      se.sessionid
      });

  5. Chuck said

    Genius!

    Thank you so much :)

  6. Muhammad Saad Mateen said

    Awesome Article, Helps me to achieve my task.

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: