LINQ to SQL中怎样创建INNER和 OUTER JOINS？

LINQ 没有为 inner 和 outer 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 ：

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

运用 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

                                                                                                        });

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

 

运用 SelectMany 和 DefaultIfEmpty:

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

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