C# LINQ Join
最后修改于 2024 年 7 月 25 日
在本文中，我们将展示如何使用 LINQ Join 操作连接数据源。
语言集成查询 (LINQ) 是一种特定领域的语言，用于查询来自各种数据源的数据，包括数组、列表、XML 文件或数据库。
Join 操作根据匹配的键将一个数据源中的对象与另一个数据源中的对象关联起来。
C# LINQ 内连接
内连接是最常见的 Join 类型。 内连接仅选择那些来自数据源且具有匹配值的记录。
Program.cs
List<Country> countries =
[
new("Slovakia", 1),
new("Hungary", 2),
new("Russia", 3),
new("Poland", 4)
];
List<City> cities =
[
new("Bratislava", 1),
new("Poprad", 1),
new("Trnava", 1),
new("Budapest", 2),
new("Miskolc", 2),
new("Ajka", 2),
new("Moscow", 3),
new("Jakutsk", 3),
new("Perm", 3),
new("Omsk", 3),
new("Warsaw", 4),
new("Krakow", 4)
];
var joined =
from country in countries
join city in cities on country.Id equals city.CountryId
select new
{
Country = country.Name,
City = city.Name
};
foreach (var e in joined)
{
Console.WriteLine($"{e.City} {e.Country}");
}
record City(string Name, int CountryId);
record Country(string Name, int Id);
我们有一个国家和城市的列表。 每个城市都包含相应的国家 ID。
var joined =
from country in countries
join city in cities on country.Id equals city.CountryId
select new
{
Country = country.Name,
City = city.Name
};
Join 操作使用 join、in、on、equals 关键字执行。
$ dotnet run Bratislava Slovakia Poprad Slovakia Trnava Slovakia Budapest Hungary Miskolc Hungary Ajka Hungary Moscow Russia Jakutsk Russia Perm Russia Omsk Russia Warsaw Poland Krakow Poland
由于在我们的数据源中，斯洛文尼亚没有列出城市，因此它不会显示在输出中。
C# LINQ 组连接
组连接基于键连接两个序列，并为每个元素对结果匹配项进行分组。
Program.cs
List<Country> countries =
[
new("Slovakia", 1),
new("Hungary", 2),
new("Russia", 3),
new("Poland", 4)
];
List<City> cities =
[
new("Bratislava", 1),
new("Poprad", 1),
new("Trnava", 1),
new("Budapest", 2),
new("Miskolc", 2),
new("Ajka", 2),
new("Moscow", 3),
new("Jakutsk", 3),
new("Perm", 3),
new("Omsk", 3),
new("Warsaw", 4),
new("Krakow", 4)
];
var groups =
from country in countries
join city in cities on country.Id equals city.CountryId into cGroup
select new
{
Country = country.Name,
Cities =
from city2 in cGroup
select city2
};
foreach (var group in groups)
{
Console.WriteLine(group.Country);
foreach (var city in group.Cities)
{
Console.WriteLine($" {city.Name}");
}
}
record City(string Name, int CountryId);
record Country(string Name, int Id);
我们没有显示每个城市映射到国家，而是显示了一个国家列表，其中相应的城市被分组。
var groups =
from country in countries
join city in cities on country.Id equals city.CountryId into cGroup
select new
{
Country = country.Name,
Cities =
from city2 in cGroup
select city2
};
组连接使用 join、in、on、equals 和 into 关键字执行。 所有城市都映射到 Cities 属性，我们对该属性执行单独的查询。
$ dotnet run Slovakia Bratislava Poprad Trnava Hungary Budapest Miskolc Ajka Russia Moscow Jakutsk Perm Omsk Poland Warsaw Krakow
C# LINQ 复合键
我们可能没有一个单一的、明确定义的键来关联两个数据源。 可以从多个值生成复合键。
Program.cs
List<Worker> workers =
[
new("John", "Doe", 1),
new("Roger", "Roe", 2),
new("Patrick", "Smith", 3),
new("Peter", "Novak", 4),
];
List<User> voters =
[
new("John", "Doe", 1),
new("Dave", "Manole", 2),
new("Patrick", "Smith", 3),
new("Paul", "Dempsey", 4),
new("Svetlana", "Malikova", 5),
new("Roger", "Roe", 6),
new("Roman", "Holz", 7),
new("Lucia", "Molnarova", 8),
new("Peter", "Novak", 9),
];
var joined =
from worker in workers
join voter in voters on new
{
worker.FirstName,
worker.LastName
} equals new
{
voter.FirstName,
voter.LastName
}
select voter.FirstName + " " + voter.LastName;
foreach (var e in joined)
{
Console.WriteLine($"{e}");
}
record User(string FirstName, string LastName, int Id);
record Worker(string FirstName, string LastName, int Id);
在该示例中，我们有一个工人和选民的列表。 我们想找出所有既是工人又是选民的人。
var joined =
from worker in workers
join voter in voters on new
{
worker.FirstName,
worker.LastName
} equals new
{
voter.FirstName,
voter.LastName
}
select voter.FirstName + " " + voter.LastName;
我们从名字和姓氏创建一个复合键。 我们假设每个人都是独一无二的。
$ dotnet run John Doe Roger Roe Patrick Smith Peter Novak
C# LINQ 多重连接
我们可以通过多个 Join 操作连接多个数据源。
Program.cs
List<Employee> employees = [
new (1, "John", "Doe"),
new (2, "Paul", "Smith"),
new (3, "Roger", "Roe")
];
List<Order> orders = [
new (1, 1, 1, 1, 1),
new (2, 1, 1, 2, 1),
new (3, 1, 2, 5, 2),
new (4, 2, 3, 4, 1),
new (5, 3, 3, 1, 1),
new (6, 3, 3, 3, 1)
];
List<Book> books = [
new (1, "War and Peace", 24.90m),
new (2, "Old Goriot", 14.5m),
new (3, "Essays and Aphorisms", 35m),
new (4, "The Call of the Wild", 11m),
new (5, "English Grammar", 33m),
];
var res = from o in orders
join Employee e in employees
on o.EmployeeId equals e.Id
join Book b in books
on o.BookId equals b.Id
orderby e.Id
select new { e.FirstName, e.LastName, b.Title, b.Price };
foreach (var e in res)
{
Console.WriteLine(e);
}
record Employee(int Id, string FirstName, string LastName);
record Book(int Id, string Title, decimal Price);
record Order(int Id, int BookOrderId, int EmployeeId, int BookId, int Quantity);
我们有三个数据源：员工、书籍和订单。 我们的目标是将每个员工映射到销售的书籍。
var res = from o in orders
join Employee e in employees
on o.EmployeeId equals e.Id
join Book b in books
on o.BookId equals b.Id
orderby e.Id
select new { e.FirstName, e.LastName, b.Title, b.Price };
通过两个 Join 操作，我们将数据组合在一起。 首先，我们将订单与员工连接起来。 然后我们添加书籍详细信息。 最后，我们知道哪些员工处理了哪些书籍。
$ dotnet run
{ FirstName = John, LastName = Doe, Title = War and Peace, Price = 24,90 }
{ FirstName = John, LastName = Doe, Title = Old Goriot, Price = 14,5 }
{ FirstName = Paul, LastName = Smith, Title = English Grammar, Price = 33 }
{ FirstName = Roger, LastName = Roe, Title = The Call of the Wild, Price = 11 }
{ FirstName = Roger, LastName = Roe, Title = War and Peace, Price = 24,90 }
{ FirstName = Roger, LastName = Roe, Title = Essays and Aphorisms, Price = 35 }
来源
在本文中，我们展示了如何使用 LINQ Join 操作连接数据源。
作者
列出所有 C# 教程。