locked
FAQ Item: How do I create INNER and OUTER JOINs in LINQ to SQL? RRS feed

  • Question

  • How do I create INNER and OUTER JOINs in LINQ to SQL?
    Sunday, June 20, 2010 1:02 PM

Answers

  • 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

    • Marked as answer by MSDN FAQ Sunday, June 20, 2010 1:03 PM
    Sunday, June 20, 2010 1:03 PM