C# Linq表连接大全(INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN、CROSS JOIN)

一、准备工作

1、建表

先创建两个表Group、User，两表的关系是N:N

CREATE TABLE [dbo].[Group](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [GroupName] [nvarchar](50) NULL,
        CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED
        (
            [Id] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

CREATE TABLE [dbo].[User](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [UserName] [nvarchar](50) NULL,
        [GroupId] [int] NULL,
        CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
        (
            [Id] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

2、测试数据

INSERT [dbo].[User] ([Id], [UserName], [GroupId]) VALUES (1, N'张1', 1)
INSERT [dbo].[User] ([Id], [UserName], [GroupId]) VALUES (2, N'张2', 2)
INSERT [dbo].[User] ([Id], [UserName], [GroupId]) VALUES (3, N'张3', 4)

INSERT [dbo].[Group] ([Id], [GroupName]) VALUES (1, N'A')
INSERT [dbo].[Group] ([Id], [GroupName]) VALUES (2, N'B')
INSERT [dbo].[Group] ([Id], [GroupName]) VALUES (3, N'C')

二、连接大全

1、内连接（INNER JOIN）

//Sql:
SELECT g.GroupName,u.UserName FROM GroupName g JOIN User u ON g.Id = u.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
    }
)

2、左连接（LEFT JOIN）

//Sql:
SELECT g.GroupName,u.UserName FROM GroupName g LEFT JOIN User u ON g.Id = u.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
    }
)

3、右连接（RIGHT JOIN）

//Sql:
SELECT g.GroupName,u.UserName FROM GroupName g RIGHT JOIN User u ON g.Id = u.GroupId

//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
    }
)

4、全连接(FULL OUTER JOIN)

//Sql:
SELECT g.GroupName,u.UserName FROM GroupName g FULL JOIN User u ON g.Id = u.GroupId

//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 ()

5、笛卡儿积(CROSS JOIN)

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
         }
)