一、准备工作
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
}
)