Linq表连接

原文地址：http://www.cnblogs.com/shenqiboy/p/3260105.html

我们知道在SQL中一共有五种JOIN操作：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')

---

3>连接大全

内连接(Inner 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 Groupsjoin u in Userson g.Id  equals u.GroupIdselect 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         }   )

---

 

左连接(Left Join)

Sql:-- Region ParametersDECLARE @p0 NVarChar(1000) = ''-- EndRegionSELECT [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 Groupsjoin u in Userson g.Id  equals u.GroupIdinto Grpfrom 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         }   )

---

 

右连接(Right Join)

Sql:-- Region ParametersDECLARE @p0 NVarChar(1000) = ''-- EndRegionSELECT     (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 Usersjoin g in Groupson u.GroupId equals g.Idinto Grpfrom 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         }   )

---

 

全连接(FULL OUTER JOIN)

Sql:

-- Region ParametersDECLARE @p0 NVarChar(1000) = ''DECLARE @p1 NVarChar(1000) = ''-- EndRegionSELECT 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 Groupsjoin u in Userson g.Id  equals u.GroupIdinto Grpfrom grp in Grp.DefaultIfEmpty()select new { GroupName=g.GroupName, UserName=(grp==null)?"":grp.UserName};var b=from u in Usersjoin g in Groupson u.GroupId equals g.Idinto Grpfrom 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 ()

笛卡儿积(cross join

Sql:SELECT [t0].[GroupName], [t1].[UserName]FROM [Group] AS [t0], [User] AS [t1]Linq to Sql:from g in Groupsfrom u in Usersselect new { GroupName=g.GroupName, UserName=u.UserName}Lambda:Groups.SelectMany (      g => Users,       (g, u) =>          new           {            GroupName = g.GroupName,             UserName = u.UserName         }   )