Linq语句实现(INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN、CROSS JOIN)

首先建立三张表如下：

分别加入数据如下：

内连接 INNER JOIN

sql:

SELECT [t0].[Id], [t0].[Name], [t1].[GroupName], [t2].[Salary] AS [Content]
FROM [User] AS [t0]
INNER JOIN [Group] AS [t1] ON [t0].[GroupId] = ([t1].[Id])
INNER JOIN [Salary] AS [t2] ON ([t0].[Id]) = [t2].[UserId]

Linq:

from u in Users
join g in Groups on u.GroupId  equals g.Id
join s in Salaries on u.Id equals s.UserId
select new
{
 u.Id,
 u.Name,
 g.GroupName,
 s.Content
}

Lambda:

Users
   .Join (
      Groups, 
      u => u.GroupId, 
      g => (Int32?)(g.Id), 
      (u, g) => 
         new  
         {
            u = u, 
            g = g
         }
   )
   .Join (
      Salaries, 
      temp0 => (Int32?)(temp0.u.Id), 
      s => s.UserId, 
      (temp0, s) => 
         new  
         {
            Id = temp0.u.Id, 
            Name = temp0.u.Name, 
            GroupName = temp0.g.GroupName, 
            Content = s.Content
         }
   )

左连接 Left Join

sql:

-- Region Parameters
DECLARE @p0 Decimal(1,0) = 0
-- EndRegion
SELECT [t0].[Id], [t0].[Name], 
    (CASE 
        WHEN [t2].[test] IS NULL THEN @p0
        ELSE CONVERT(Decimal(29),[t2].[Salary])
     END) AS [Content]
FROM [User] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[Salary], [t1].[UserId]
    FROM [Salary] AS [t1]
    ) AS [t2] ON ([t0].[Id]) = [t2].[UserId]

linq：

from u in Users
join s in Salaries on u.Id equals s.UserId into NewSalaries
from n in NewSalaries.DefaultIfEmpty()
select new
{
 u.Id,
 u.Name,
 Content = n==null?0:n.Content
}

Lambda:

Users
   .GroupJoin (
      Salaries, 
      u => (Int32?)(u.Id), 
      s => s.UserId, 
      (u, NewSalaries) => 
         new  
         {
            u = u, 
            NewSalaries = NewSalaries
         }
   )
   .SelectMany (
      temp0 => temp0.NewSalaries.DefaultIfEmpty (), 
      (temp0, n) => 
         new  
         {
            Id = temp0.u.Id, 
            Name = temp0.u.Name, 
            Content = (n == null) ? (Decimal?)0 : n.Content
         }
   )

右连接 RIGHT JOIN

和左连接一样。

混合使用

sql

SELECT [t0].[Id], [t0].[Name], [t1].[GroupName], 
    (CASE 
        WHEN [t3].[test] IS NULL THEN @p0
        ELSE CONVERT(Decimal(29),[t3].[Salary])
     END) AS [Content]
FROM [User] AS [t0]
INNER JOIN [Group] AS [t1] ON [t0].[GroupId] = ([t1].[Id])
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t2].[Salary], [t2].[UserId]
    FROM [Salary] AS [t2]
    ) AS [t3] ON ([t0].[Id]) = [t3].[UserId]

linq

from u in Users
join g in Groups on u.GroupId  equals g.Id
join s in Salaries on u.Id equals s.UserId into NewSalaries
from n in NewSalaries.DefaultIfEmpty()
select new
{
 u.Id,
 u.Name,
 g.GroupName,
 Content = n==null?0:n.Content
}

Lambda:

Users
   .Join (
      Groups, 
      u => u.GroupId, 
      g => (Int32?)(g.Id), 
      (u, g) => 
         new  
         {
            u = u, 
            g = g
         }
   )
   .GroupJoin (
      Salaries, 
      temp0 => (Int32?)(temp0.u.Id), 
      s => s.UserId, 
      (temp0, NewSalaries) => 
         new  
         {
            <>h__TransparentIdentifier0 = temp0, 
            NewSalaries = NewSalaries
         }
   )
   .SelectMany (
      temp1 => temp1.NewSalaries.DefaultIfEmpty (), 
      (temp1, n) => 
         new  
         {
            Id = temp1.<>h__TransparentIdentifier0.u.Id, 
            Name = temp1.<>h__TransparentIdentifier0.u.Name, 
            GroupName = temp1.<>h__TransparentIdentifier0.g.GroupName, 
            Content = (n == null) ? (Decimal?)0 : n.Content
         }
   )

这里推荐一个很好用的Linq语句测试工具