在我们工作中表连接是很常用的，但常用的有这三种连接方式：左连接、右连接、内链接
在本章节中讲的是
1、如何在Linq中使用左连接，右连接，内连接。
2、三种连接之间的特点在哪？
3、Linq的三种连接语法是怎么样的呢（我觉得左右连接也就相当换个位置）
一、SQL Server 中的三种连接
首先我们示范以下SQL中的左连接，右连接，内连接，需要准备两张表：
CREATE TABLE [dbo].[Company](
[Id] int identity Primary key,
[Code] uniqueidentifier,
[CompanyName] nvarchar(20) null,
[Address] nvarchar(100) null
)
CREATE TABLE [dbo].[People](
[Id] int identity Primary key,
[CompanyCode] uniqueidentifier null,
[Name] nvarchar(20) null,
[Age] Int null,
)
数据准备
Company
People
在SQL表连接 LEFT JOIN，RIGHT JOIN , INNER JOIN (JOIN)
--通过LEFT JOIN 关键字很明显看出是左连接
SELECT * FROM [People] p LEFT JOIN Company c on p.CompanyCode = c.Code --通过RIGHT JOIN 关键字很明显看出是右连接
SELECT * FROM [Company] c RIGHT JOIN [People] p on p.CompanyCode = c.Code --通过INNER JOIN 关键字很明显看出是内连接
SELECT * FROM [People] p INNER JOIN [Company] c on p.CompanyCode = c.Code
执行后的结果
各连接的特点：
左连接和右连接很好理解，看字面意思就可以知道左连接是按照左边的数据进行显示，如果右边的跟左边的匹配不到则右边的数据会为空，右连接相反
内链接也很好理解，看图可以看出来只要能匹配上的就显示不能匹配的就不显示数据
那么在C# Linq中我们如何实现表连接呢？
二、C# 中使用Linq实现集合之间的连接
同样先准备下类和数据源
Company和People 类
public class Company
{
public int Id { get; set; }
public Guid Code { get; set; }
public string CompanyName { get; set; }
public string Address { get; set; }
} public class People
{
public int Id { get; set; }
public Guid CompanyCode { get; set; }
public string Name { get; set; }
public int Age { get; set; }
}
初始数据
List<Company> companyList = new List<Company>(){
new Company() {
Id=,
Code = new Guid("B0C5900C-DC15-4F9B-A2C0-25D79B2987E2"),
CompanyName = "菜鸟公司",
Address = "菜鸟地址",
},
new Company() {
Id=,
Code = new Guid("FA46534F-E5FA-48DD-8C67-58A8FC5573FB"),
CompanyName = "中级地址",
Address = "中级地址",
},
new Company() {
Id=,
Code = new Guid("8188B6E3-1FEA-462F-83A5-A6610996DB42"),
CompanyName = "牛X公司",
Address = "牛X地址",
},
new Company() {
Id=,
Code = new Guid("64CD13EB-398A-4DC2-BFBF-F4032A40DC10"),
CompanyName = "黑马公司",
Address = "黑马地址",
},
};
List<People> peopleList = new List<People>() {
new People() {
Id = ,
CompanyCode = new Guid("B0C5900C-DC15-4F9B-A2C0-25D79B2987E2"),
Name = "Tony",
Age =
},
new People() {
Id = ,
CompanyCode = new Guid("B0C5900C-DC15-4F9B-A2C0-25D79B2987E2"),
Name = "Ramon",
Age =
},
new People() {
Id = ,
CompanyCode = new Guid("FA46534F-E5FA-48DD-8C67-58A8FC5573FB"),
Name = "Ronnin",
Age =
},
new People() {
Id = ,
CompanyCode = new Guid("8188B6E3-1FEA-462F-83A5-A6610996DB42"),
Name = "Vi Vi",
Age =
},
new People() {
Id = ,
CompanyCode = new Guid("E0E04E6B-4E0F-4A83-AFDB-30427221448E"),
Name = "Gary Chan",
Age =
},
};
Linq连接代码
{
Console.WriteLine("-----------------------------------------------------LEFT JION---------------------------------------------------------");
var qurey = from c in companyList
join p in peopleList
on c.Code equals p.CompanyCode into joinCompanyList
from p in joinCompanyList.DefaultIfEmpty()
select new
{
Id = c?.Id,
CompanyName = c?.CompanyName,
CompanyAddress = c?.Address,
peopleName = p?.Name,
peopleAge = p?.Age
};
foreach (var item in qurey)
{
Console.WriteLine($"公司ID:{item.Id},公司名称:{item.CompanyName},公司地址:{item.CompanyAddress},个人名称:{item.peopleName},个人年龄:{item.peopleAge}");
}
}
{
Console.WriteLine("-----------------------------------------------------RIGHT JION---------------------------------------------------------");
var qurey = from p in peopleList
join c in companyList
on p.CompanyCode equals c.Code into joinCompanyList
from c in joinCompanyList.DefaultIfEmpty()
select new
{
Id = c?.Id,
CompanyName = c?.CompanyName,
CompanyAddress = c?.Address,
peopleName = p?.Name,
peopleAge = p?.Age
};
foreach (var item in qurey)
{
Console.WriteLine($"公司ID:{item.Id},公司名称:{item.CompanyName},公司地址:{item.CompanyAddress},个人名称:{item.peopleName},个人年龄:{item.peopleAge}");
}
}
{
Console.WriteLine("-----------------------------------------------------INNER JOIN---------------------------------------------------------");
var qurey = from p in peopleList
join c in companyList
on p.CompanyCode equals c.Code
select new
{
Id = c?.Id,
CompanyName = c?.CompanyName,
CompanyAddress = c?.Address,
peopleName = p?.Name,
peopleAge = p?.Age
};
foreach (var item in qurey)
{
Console.WriteLine($"公司ID:{item.Id},公司名称:{item.CompanyName},公司地址:{item.CompanyAddress},个人名称:{item.peopleName},个人年龄:{item.peopleAge}");
}
}
结果
注：上例中使用了DefaultIfEmpty操作符，它能够为实序列提供一个默认的元素。DefaultIfEmpty使用了泛型中的default关键字。default关键字对于引用类型将返回null,而对于值类型则返回0。对于结构体类型，则会根据其成员类型将它们相应地初始化为null(引用类型)或0(值类型)
本章节就到这里啦，坚持就是胜利.....................