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

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

我们知道在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>连接大全

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

 

---

Linq实现LEFT OUT JOIN

原文地址：http://geekswithblogs.net/AzamSharp/archive/2008/04/07/121103.aspx

Suppose you have a tblRoom and tblUserInfo. Now, you need to select all the rooms regardless of whether the room has user information or not. This calls for a LEFT JOIN which will select everything from the LEFT side (the room side) regardless of the join on the right side. Here is the example.

假如你有两张表tblRoom（房间表）和tblUserInfo（住户表）。现在你需要检索出所有房间的信息，而不管这个房间是否有人居住。这就需要进行LEFT JOIN（左外连接），左外连接会检索出LEFT JOIN左边表中的所有行，而不管右边的表是否有匹配项。下面是一个例子：

var list = from r in dc.tblRooms
                          join ui in dc.tblUserInfos
                          on r.UserName equals ui.UserNameinto userrooms

                          from ur in userrooms.DefaultIfEmpty()

                          select new
                           {
                               FirstName = (ur.FirstName == null) ? "N/A" : ur.FirstName,
                               LastName = (ur.LastName == null) ? "N/A" : ur.LastName,
                               RoomName = r.Name
                           };

The anonymous type replaces the "null" FirstName and LastName with "N/A" (not available).

使用"N/A"（不可得）代替 FirstName 和 LastName 值为"null"的情况。

 

另附：Linq实现多个表 LEFT JOIN 如下

来自：http://hi.baidu.com/xuejianxiyang/item/0c9df0175c8dbdfaddeecae3

目标SQL语句（多表 LEFT JOIN 查询）

目标SQL语句（多表 LEFT JOIN 查询）
SELECT id, name, jname, cname
    FROM userinfo u
    LEFT JOIN job j on u.job = j.jid
    LEFT JOIN city c on u.city = c.cid

Linq To Sql 实现三个表 LEFT JOIN 如下：

var list = (
from u in dc.userinfos
    join j in dc.jobs on u.job equals j.jid into j_join
from x in j_join.DefaultIfEmpty()
    join c in dc.cities on u.city equals c.cid into c_join
from v in c_join.DefaultIfEmpty()
select new
{
    id = u.id,
    name = u.name,
    jname = x.jname,
    cname = v.cname,
    /*u1=u,x1=x,v1=v*/
    //不要用对象的方式 因为对象可能为null那么对象.属性就会抛异常
}
).ToList();

for (var i = 0; i < list.Count(); i++)
{
    Console.WriteLine(list[i].name + '\t' + list[i].jname + '\t' + list[i].cname); //字段为null不报异常
    //Console.WriteLine(list[i].u1.name+'\t'+list[i].x1.jname+'\t'+list[i].v1.cname+"\r\n"); //对象x1 v1 有可能为null 抛异常
}
Console.ReadLine();

我用Linq自己写的3个表 LEFT JOIN 例子：

Emp（员工表）、Dept（部门表）、KqEmp（人员考勤信息表）

 

顶

0