转载http://www.cnblogs.com/shenqiboy/p/3260105.html

我们知道在SQL中一共有五种JOIN操作：INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN、CROSS JOIN

内连接、
Sql:
SELECT [t0].[GroupName], [t1].[UserName]
FROM [Group] AS [t0]
INNER JOIN [User] AS [t1] ON ([t0].[Id]) = [t1].[GroupId]

Linq to Sql:
from g in Groups
join u in Users
on g.Id  equals u.GroupId
select new { GroupName=g.GroupName, UserName=u.UserName}

Lambda:
Groups.Join 
(
      Users, 
      g => (Int32?)(g.Id), 
      u => u.GroupId, 
      (g, u) => 
         new  
         {
            GroupName = g.GroupName, 
            UserName = u.UserName
         }
   )

左连接、

Sql:
-- Region Parameters
DECLARE @p0 NVarChar(1000) = ''
-- EndRegion
SELECT [t0].[GroupName], 
    (CASE 
        WHEN [t2].[test] IS NULL THEN CONVERT(NVarChar(50),@p0)
        ELSE [t2].[UserName]
     END) AS [UserName]
FROM [Group] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[UserName], [t1].[GroupId]
    FROM [User] AS [t1]
    ) AS [t2] ON ([t0].[Id]) = [t2].[GroupId]

Linq to Sql:
from g in Groups
join u in Users
on g.Id  equals u.GroupId
into Grp
from grp in Grp.DefaultIfEmpty()
select new { GroupName=g.GroupName, UserName=(grp==null)?"":grp.UserName}

Lambda:
Groups.GroupJoin (
      Users, 
      g => (Int32?)(g.Id), 
      u => u.GroupId, 
      (g, Grp) => 
         new  
         {
            g = g, 
            Grp = Grp
         }
   ) .SelectMany (
      temp0 => temp0.Grp.DefaultIfEmpty (), 
      (temp0, grp) => 
         new  
         {
            GroupName = temp0.g.GroupName, 
            UserName = (grp == null) ? "" : grp.UserName
         }
   )

右连接、

Sql:
-- Region Parameters
DECLARE @p0 NVarChar(1000) = ''
-- EndRegion
SELECT 
    (CASE 
        WHEN [t2].[test] IS NULL THEN CONVERT(NVarChar(50),@p0)
        ELSE [t2].[GroupName]
     END) AS [GroupName], [t0].[UserName]
FROM [User] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[Id], [t1].[GroupName]
    FROM [Group] AS [t1]
    ) AS [t2] ON [t0].[GroupId] = ([t2].[Id])

Linq to Sql:
from u in Users
join g in Groups
on u.GroupId equals g.Id
into Grp
from grp in Grp.DefaultIfEmpty()
select new { GroupName=(grp==null)?"":grp.GroupName, UserName=u.UserName}

Lambda:
Users.GroupJoin (
      Groups, 
      u => u.GroupId, 
      g => (Int32?)(g.Id), 
      (u, Grp) => 
         new  
         {
            u = u, 
            Grp = Grp
         }
   ).SelectMany (
      temp0 => temp0.Grp.DefaultIfEmpty (), 
      (temp0, grp) => 
         new  
         {
            GroupName = (grp == null) ? "" : grp.GroupName, 
            UserName = temp0.u.UserName
         }
   )

全连接、

Sql:
-- Region Parameters
DECLARE @p0 NVarChar(1000) = ''
DECLARE @p1 NVarChar(1000) = ''
-- EndRegion
SELECT DISTINCT [t7].[GroupName], [t7].[value] AS [UserName]
FROM (
    SELECT [t6].[GroupName], [t6].[value]
    FROM (
        SELECT [t0].[GroupName], 
            (CASE 
                WHEN [t2].[test] IS NULL THEN CONVERT(NVarChar(50),@p0)
                ELSE [t2].[UserName]
             END) AS [value]
        FROM [Group] AS [t0]
        LEFT OUTER JOIN (
            SELECT 1 AS [test], [t1].[UserName], [t1].[GroupId]
            FROM [User] AS [t1]
            ) AS [t2] ON ([t0].[Id]) = [t2].[GroupId]
        UNION ALL
        SELECT 
            (CASE 
                WHEN [t5].[test] IS NULL THEN CONVERT(NVarChar(50),@p1)
                ELSE [t5].[GroupName]
             END) AS [value], [t3].[UserName]
        FROM [User] AS [t3]
        LEFT OUTER JOIN (
            SELECT 1 AS [test], [t4].[Id], [t4].[GroupName]
            FROM [Group] AS [t4]
            ) AS [t5] ON [t3].[GroupId] = ([t5].[Id])
        ) AS [t6]
    ) AS [t7]

Linq to Sql:
var a=from g in Groups
join u in Users
on g.Id  equals u.GroupId
into Grp
from grp in Grp.DefaultIfEmpty()
select new { GroupName=g.GroupName, UserName=(grp==null)?"":grp.UserName};

var b=from u in Users
join g in Groups
on u.GroupId equals g.Id
into Grp
from grp in Grp.DefaultIfEmpty()
select new { GroupName=(grp==null)?"":grp.GroupName, UserName=u.UserName};

var c=a.Concat(b).Distinct();
c.Dump();

Lambda:
Groups
   .GroupJoin (
      Users, 
      g => (Int32?)(g.Id), 
      u => u.GroupId, 
      (g, Grp) => 
         new  
         {
            g = g, 
            Grp = Grp
         }
   )
   .SelectMany (
      temp0 => temp0.Grp.DefaultIfEmpty (), 
      (temp0, grp) => 
         new  
         {
            GroupName = temp0.g.GroupName, 
            UserName = (grp == null) ? "" : grp.UserName
         }
   )
   .Concat (
      Users
         .GroupJoin (
            Groups, 
            u => u.GroupId, 
            g => (Int32?)(g.Id), 
            (u, Grp) => 
               new  
               {
                  u = u, 
                  Grp = Grp
               }
         )
         .SelectMany (
            temp2 => temp2.Grp.DefaultIfEmpty (), 
            (temp2, grp) => 
               new  
               {
                  GroupName = (grp == null) ? "" : grp.GroupName, 
                  UserName = temp2.u.UserName
               }
         )
   )
   .Distinct ()

笛卡尔积、

Sql:
SELECT [t0].[GroupName], [t1].[UserName]
FROM [Group] AS [t0], [User] AS [t1]

Linq to Sql:
from g in Groups
from u in Users
select new { GroupName=g.GroupName, UserName=u.UserName}

Lambda:
Groups.SelectMany 
(
      g => Users, 
      (g, u) => 
         new  
         {
            GroupName = g.GroupName, 
            UserName = u.UserName
         }
   )

--------------------------------------------------------------------------------------------------------------------------------------------------------------

全连接（Full Outer Join)，Linq并不支持，所以就需要使用其它方法来实现，就像不支持全连接的数据库一样，先使用内连接找出公共的部分，然后分别找出左连接和右连接的部分，把这三部分的结果UNION一下，即可得到全连接的效果。

看问题：

实现方法一：

先找出左连接的结果，再找出右连接的结果，然后把这两个结果UNION即可，UNION时会自动把重复的数据过滤掉。

方法二：

得到左连接的数据，再从右连接的数据中把左连接的数据排除掉，两者的数据Concat一下即可

以上两种方法，其实是同一种方式来实现全连接的，只是第二次拿出的数据有所不同。