Linq中的Join使用笔记

关于Linq中的join使用笔记

在进行数据库操作时，经常会涉及到多表联合查询，这时就需要用到join。而连接也分为以下两种：

• 内连接(INNER JOIN)：是最常见的一种连接，只连接匹配的行。

• 外连接：分为左连接(LEFT JOIN)，右连接(RIGHT JOIN)以及全连接(FULL OUTER JOIN)。

但在Linq中，在进行连接时只有join这样一个关键词，所以在需要用Linq实现不同的连接时就需要一些另外的操作。
测试数据如下：

Id	GroupName
1	A
2	B
3	C

Id	UserName	GroupId
1	王1	1
1	王2	2
1	王3	4

• Linq实现内连接

from g in Groups
join u in Users
on g.Id  equals u.GroupId
select new { GroupName=g.GroupName, UserName=u.UserName}

对应SQL语句为

SELECT [t0].[GroupName], [t1].[UserName]
FROM [Group] AS [t0]
INNER JOIN [User] AS [t1] ON ([t0].[Id]) = [t1].[GroupId]

结果如下：

GroupName	UserName
A	张1
B	张2

• Linq实现左连接

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}

对应Sql为

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]

结果为：

GroupName	UserName
A	王1
B	王2
C

• Linq实现右连接

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}

对应Sql

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

结果为

GroupName	UserName
A	王1
B	王2
	王3

• Linq实现全连接

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

对应Sql

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]

结果为

GroupName	UserName
	王3
A	王1
B	王2
C