转载http://www.cnblogs.com/shenqiboy/p/3260105.html
我们知道在SQL中一共有五种JOIN操作：INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN、CROSS 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 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
}
)
--------------------------------------------------------------------------------------------------------------------------------------------------------------
全连接（Full Outer Join)，Linq并不支持，所以就需要使用其它方法来实现，就像不支持全连接的数据库一样，先使用内连接找出公共的部分，然后分别找出左连接和右连接的部分，把这三部分的结果UNION一下，即可得到全连接的效果。
看问题：
|
1
2
3
4
5
6
7
8
9
|
ID FirstName-- --------- 1 John 2 SueID LastName-- -------- 1 Doe 3 Smith |
|
1
|
显示下面的结果： |
|
1
2
3
4
5
|
ID FirstName LastName-- --------- -------- 1 John Doe 2 Sue 3 Smith |
实现方法一：
先找出左连接的结果，再找出右连接的结果，然后把这两个结果UNION即可，UNION时会自动把重复的数据过滤掉。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
var firstNames = new[]{ new { ID = 1, Name = "John" }, new { ID = 2, Name = "Sue" },};var lastNames = new[]{ new { ID = 1, Name = "Doe" }, new { ID = 3, Name = "Smith" },};var leftOuterJoin = from first in firstNames join last in lastNames on first.ID equals last.ID into temp from last in temp.DefaultIfEmpty(new { first.ID, Name = default(string) }) select new { first.ID, FirstName = first.Name, LastName = last.Name, };var rightOuterJoin = from last in lastNames join first in firstNames on last.ID equals first.ID into temp from first in temp.DefaultIfEmpty(new { last.ID, Name = default(string) }) select new { last.ID, FirstName = first.Name, LastName = last.Name, };var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin); |
方法二：
得到左连接的数据，再从右连接的数据中把左连接的数据排除掉，两者的数据Concat一下即可
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
var firstNames = new[]{ new { ID = 1, Name = "John" }, new { ID = 2, Name = "Sue" },};var lastNames = new[]{ new { ID = 1, Name = "Doe" }, new { ID = 3, Name = "Smith" },};var leftData = (from first in firstNames join last in lastNames on first.ID equals last.ID into temp from last in temp.DefaultIfEmpty(new { first.ID, Name = default(string) }) select new { first.ID, FirstName = first.Name, LastName = last.Name, });var rightRemainingData = (from r in lastNames where !(from a in leftData select a.ID).Contains(r.ID) select new { r.ID, FirstName = default(string), LastName = r.Name });var fullOuterjoinData = leftData.Concat(rightRemainingData); |
以上两种方法，其实是同一种方式来实现全连接的，只是第二次拿出的数据有所不同。