none
Left outer join RRS feed

Answers

  • Hi Garvander,

     

    I once wrote a LINQ to SQL FAQ about this question, but it has not been released.    I guess I can share it here first.  J

     

    "

    How do I create INNER and OUTER JOINs in LINQ to SQL?

    Although LINQ does not define any keywords for inner or outer joins, we can use the existing LINQ join grammar to implement them.    Suppose we have a sequence of departments and a sequence of courses, then we can perform the inner and outer joins as following:

     

    INNER JOIN:

    Generally, the default behavior of the JOIN operator is an “inner join”.    When joining a sequence of departments with a sequence of courses, we only get the departments which have one or more related courses.

     

    Using JOIN operator:

    ===================================================================
    var query = from d in db.Departments

                         join c in db.Courses

                         on d.DepartmentID equals c.DepartmentID

                         select new

                         {

                             DepartmentName = d.Name,

                             CourseName = c.Title

                         };
    ===================================================================

     

    Using JOIN extension method:

    ===================================================================

    var query = db.Departments.Join(db.Courses,

                                                                  d => d.DepartmentID,

                                                                  c => d.DepartmentID,

                                                                  (d, c) => new

                                                                                 {

                                                                                     DepartmentName = d.Name,

                                                                                     CourseName = c.Title

                                                                                 });

    ===================================================================

     

    OUTER JOIN:

    We can use the GroupJoin operator as the SQL left outer join.   The compiler differentiate the GroupJoin and the Join operator by the into keyword in the next clause of the join clause.    If we need to enumerate each element of the inner sequence project a null value when no course exists for a given department, the job is left for the SelectMany and DefaultIfEmpty extension methods.  

     

    Using GroupJoin operator:

    ===================================================================

    var query = from d in db.Departments

                         join c in db.Courses

                         on d.DepartmetnID equals c.DepartmentID into courseList

                         select new

                         {

                             DepartmentName = d.Name,

                             Courses = courseList

                         };

    ===================================================================

     

    Using GroupJoin extension method:

    ===================================================================

    var query = db.Departments.GroupJoin(db.Courses,

                                                                             d => d.DepartmentID,

                                                                             c => c.DepartmentID,

                                                                             (d, courseList) => new

                                                                                                            {

                                                                                                                 DepartmentName = d.Name,

                                                                                                                 Courses = courseList

                                                                                                            });

    ===================================================================

     

    Using SelectMany and DefaultIfEmpty:

    ===================================================================

    var query = from d in db.Departments

                         join c in db.Courses

                         on d.DepartmentID equals c.DepartmentID into courseList

                         from cl in courseList.DefaultIfEmpty()

                         select new

                         {

                              DepartmentName = d.Name,

                              CourseName = cl == null ? string.Empty : cl.Title

                         }

    ===================================================================

     

    Related threads:

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/fb6a48ea-cd98-4304-95a9-bd4534523fec

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/b6f2beff-ccdb-419d-9464-08a977d1bce5

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/541706be-8d21-4618-a0c0-72fa2ffdd047

     

    Related references:

    http://codeexperiment.com/post/LINQ-To-SQL-Outer-Joins.aspx

    http://odetocode.com/blogs/scott/archive/2008/03/24/inner-outer-lets-all-join-together-with-linq.aspx

     

    VB.NET queries:

    http://www.codeguru.com/vb/vb_linq/article.php/c15057__1/Implementing-a-Left-Join-with-LINQ.htm

    "

     

    Good day!  

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, June 2, 2010 1:11 AM
    Moderator
  • Hi Garvander,

     

    I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Garvander Tuesday, June 8, 2010 5:55 PM
    Friday, June 4, 2010 1:26 AM
    Moderator
  • Friday, June 4, 2010 1:47 AM

All replies

  • Hi Garvander,

     

    I once wrote a LINQ to SQL FAQ about this question, but it has not been released.    I guess I can share it here first.  J

     

    "

    How do I create INNER and OUTER JOINs in LINQ to SQL?

    Although LINQ does not define any keywords for inner or outer joins, we can use the existing LINQ join grammar to implement them.    Suppose we have a sequence of departments and a sequence of courses, then we can perform the inner and outer joins as following:

     

    INNER JOIN:

    Generally, the default behavior of the JOIN operator is an “inner join”.    When joining a sequence of departments with a sequence of courses, we only get the departments which have one or more related courses.

     

    Using JOIN operator:

    ===================================================================
    var query = from d in db.Departments

                         join c in db.Courses

                         on d.DepartmentID equals c.DepartmentID

                         select new

                         {

                             DepartmentName = d.Name,

                             CourseName = c.Title

                         };
    ===================================================================

     

    Using JOIN extension method:

    ===================================================================

    var query = db.Departments.Join(db.Courses,

                                                                  d => d.DepartmentID,

                                                                  c => d.DepartmentID,

                                                                  (d, c) => new

                                                                                 {

                                                                                     DepartmentName = d.Name,

                                                                                     CourseName = c.Title

                                                                                 });

    ===================================================================

     

    OUTER JOIN:

    We can use the GroupJoin operator as the SQL left outer join.   The compiler differentiate the GroupJoin and the Join operator by the into keyword in the next clause of the join clause.    If we need to enumerate each element of the inner sequence project a null value when no course exists for a given department, the job is left for the SelectMany and DefaultIfEmpty extension methods.  

     

    Using GroupJoin operator:

    ===================================================================

    var query = from d in db.Departments

                         join c in db.Courses

                         on d.DepartmetnID equals c.DepartmentID into courseList

                         select new

                         {

                             DepartmentName = d.Name,

                             Courses = courseList

                         };

    ===================================================================

     

    Using GroupJoin extension method:

    ===================================================================

    var query = db.Departments.GroupJoin(db.Courses,

                                                                             d => d.DepartmentID,

                                                                             c => c.DepartmentID,

                                                                             (d, courseList) => new

                                                                                                            {

                                                                                                                 DepartmentName = d.Name,

                                                                                                                 Courses = courseList

                                                                                                            });

    ===================================================================

     

    Using SelectMany and DefaultIfEmpty:

    ===================================================================

    var query = from d in db.Departments

                         join c in db.Courses

                         on d.DepartmentID equals c.DepartmentID into courseList

                         from cl in courseList.DefaultIfEmpty()

                         select new

                         {

                              DepartmentName = d.Name,

                              CourseName = cl == null ? string.Empty : cl.Title

                         }

    ===================================================================

     

    Related threads:

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/fb6a48ea-cd98-4304-95a9-bd4534523fec

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/b6f2beff-ccdb-419d-9464-08a977d1bce5

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/541706be-8d21-4618-a0c0-72fa2ffdd047

     

    Related references:

    http://codeexperiment.com/post/LINQ-To-SQL-Outer-Joins.aspx

    http://odetocode.com/blogs/scott/archive/2008/03/24/inner-outer-lets-all-join-together-with-linq.aspx

     

    VB.NET queries:

    http://www.codeguru.com/vb/vb_linq/article.php/c15057__1/Implementing-a-Left-Join-with-LINQ.htm

    "

     

    Good day!  

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, June 2, 2010 1:11 AM
    Moderator
  • Hi Garvander,

     

    I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Garvander Tuesday, June 8, 2010 5:55 PM
    Friday, June 4, 2010 1:26 AM
    Moderator
  • Friday, June 4, 2010 1:47 AM