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 Usersjoin g in Groups on u.GroupId  equals g.Idjoin s in Salaries on u.Id equals s.UserIdselect 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 ParametersDECLARE @p0 Decimal(1,0) = 0-- EndRegionSELECT [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 Usersjoin s in Salaries on u.Id equals s.UserId into NewSalariesfrom 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 Usersjoin g in Groups on u.GroupId  equals g.Idjoin s in Salaries on u.Id equals s.UserId into NewSalariesfrom 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语句测试工具