locked
FAQs: 在LINQ to SQL中怎样创建INNER和 OUTER JOINS? RRS feed

答案

  • 尽管LINQ没有为innerouter joins定义任何关键字,但是我们可以用现有LINQ join语法来实现他们。假设我们有一个部门的序列和课程的序列,那么我们可以执行的内部和外部连接如下:

    INNER JOIN

    通常来讲,JOIN操作的默认行为是“inner join”当一个课程序列加入一个部门序列的时候,我们只能得到那些有一个或者多个相关课程的部门。

    使用JOIN运算符:

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

                         join c in db.Courses

                         ond.DepartmentID equals c.DepartmentID

                         select new

                         {

                             DepartmentName = d.Name,

                             CourseName = c.Title

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

     

    使用JOIN扩展方法:

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

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

                                                                  d =>d.DepartmentID,

                                                                  c =>d.DepartmentID,

                                                                  (d, c) =>new

                                                                                 {

                                                                                     DepartmentName = d.Name,

                                                                                     CourseName = c.Title

                                                                                 });

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

    OUTER JOIN

    我们可以像用SQLleft outer join一样用GroupJoin运算符。编译器通过在Join语句的下一语句加入关键字来区分GroupJoinJoin运算符。,如果我们想要在给定的部门没有一个课程存在的时候枚举项目内部序列的每一个元素是空值,这种工作就留给SelectManyDefaultIfEnpty扩展方法去做。

    运用GroupJoin运算符:

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

    var query = from d in db.Departments

                         join c in db.Courses

                         ond.DepartmetnID equals c.DepartmentID into courseList

                         select new

                         {

                             DepartmentName = d.Name,

                             Courses = courseList

                         };

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

    运用GroupJoin扩展方法:

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

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

                                                                             d =>d.DepartmentID,

                                                                             c =>c.DepartmentID,

                                                                             (d, courseList) =>new

                                                                                                            {

                                                                                                                 DepartmentName = d.Name,

                                                                                                                 Courses = courseList

                                                                                                            });

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

     

    运用SelectManyDefaultIfEmpty:

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

    var query = from d in db.Departments

                         join c in db.Courses

                         ond.DepartmentID equals c.DepartmentID into courseList

                         from cl in courseList.DefaultIfEmpty()

                         select new

                         {

                              DepartmentName = d.Name,

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

                         }

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

    相关帖子:

    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

     

    相关参考:

    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 查询:

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

    2011年5月3日 8:55