方法 : シーケンスの要素を並べ替える (LINQ to SQL)
1 つ以上のキーに従ってシーケンスを並べ替えるには、OrderBy 演算子を使用します。
.gif "メモ") メモ |
|---|
LINQ to SQL は、string や int などの単純で基本的なデータ型による順序付けをサポートするように設計されています。匿名型のように複数の値を持つ複雑なクラスでの順序付けはサポートされていません。また、byte データ型もサポートされていません。 |
使用例
次の例は、Employees を入社日の順に並べ替えます。
Dim hireQuery = _
From emp In db.Employees _
Select emp _
Order By emp.HireDate
For Each empObj As Employee In hireQuery
Console.WriteLine("EmpID = {0}, Date Hired = {1}", _
empObj.EmployeeID, empObj.HireDate)
Next
IOrderedQueryable<Employee> hireQuery =
from emp in db.Employees
orderby emp.HireDate
select emp;
foreach (Employee empObj in hireQuery)
{
Console.WriteLine("EmpID = {0}, Date Hired = {1}",
empObj.EmployeeID, empObj.HireDate);
}
次の例は、where を使用して、London に出荷された Orders を運送料の順に並べ替えます。
Dim freightQuery = _
From ord In db.Orders _
Where ord.ShipCity = "London" _
Select ord _
Order By ord.Freight
For Each ordObj In freightQuery
Console.WriteLine("Order ID = {0}, Freight = {1}", _
ordObj.OrderID, ordObj.Freight)
Next
IOrderedQueryable<Order> freightQuery =
from ord in db.Orders
where ord.ShipCity == "London"
orderby ord.Freight
select ord;
foreach (Order ordObj in freightQuery)
{
Console.WriteLine("Order ID = {0}, Freight = {1}",
ordObj.OrderID, ordObj.Freight);
}
次の例は、Products を単価の高い順に並べ替えます。
Dim priceQuery = _
From prod In db.Products _
Select prod _
Order By prod.UnitPrice Descending
For Each prodObj In priceQuery
Console.WriteLine("Product ID = {0}, Unit Price = {1}", _
prodObj.ProductID, prodObj.UnitPrice)
Next
IOrderedQueryable<Product> priceQuery =
from prod in db.Products
orderby prod.UnitPrice descending
select prod;
foreach (Product prodObj in priceQuery)
{
Console.WriteLine("Product ID = {0}, Unit Price = {1}",
prodObj.ProductID, prodObj.UnitPrice);
}
次の例は、OrderBy で複数のフィールドを使用して、Customers を最初に都市の順に、次に連絡先の順に並べ替えます。
Dim custQuery = _
From cust In db.Customers _
Select cust _
Order By cust.City, cust.ContactName
For Each custObj In custQuery
Console.WriteLine("City = {0}, Name = {1}", custObj.City, _
custObj.ContactName)
Next
IOrderedQueryable<Customer> custQuery =
from cust in db.Customers
orderby cust.City, cust.ContactName
select cust;
foreach (Customer custObj in custQuery)
{
Console.WriteLine("City = {0}, Name = {1}", custObj.City,
custObj.ContactName);
}
次の例は、EmployeeID 1 からの注文を、最初に出荷先国の順に、次に運送料の高い順に並べ替えます。
Dim ordQuery = _
From ord In db.Orders _
Where CInt(ord.EmployeeID.Value) = 1 _
Select ord _
Order By ord.ShipCountry, ord.Freight Descending
For Each ordObj In ordQuery
Console.WriteLine("Country = {0}, Freight = {1}", _
ordObj.ShipCountry, ordObj.Freight)
Next
IOrderedQueryable<Order> ordQuery =
from ord in db.Orders
where ord.EmployeeID == 1
orderby ord.ShipCountry, ord.Freight descending
select ord;
foreach (Order ordObj in ordQuery)
{
Console.WriteLine("Country = {0}, Freight = {1}",
ordObj.ShipCountry, ordObj.Freight);
}
次の例は、OrderBy 演算子、Max 演算子、および GroupBy 演算子を組み合わせて、各カテゴリで単価の最も高い Products を特定し、そのグループをカテゴリ ID の順に並べ替えます。
Dim highPriceQuery = From prod In db.Products _
Group prod By prod.CategoryID Into grouping = Group _
Order By CategoryID _
Select CategoryID, _
MostExpensiveProducts = _
From prod2 In grouping _
Where prod2.UnitPrice = _
grouping.Max(Function(p3) p3.UnitPrice)
For Each prodObj In highPriceQuery
Console.WriteLine(prodObj.CategoryID)
For Each listing In prodObj.MostExpensiveProducts
Console.WriteLine(listing.ProductName)
Next
Next
var highPriceQuery =
from prod in db.Products
group prod by prod.CategoryID into grouping
orderby grouping.Key
select new
{
grouping.Key,
MostExpensiveProducts =
from prod2 in grouping
where prod2.UnitPrice == grouping.Max(p3 => p3.UnitPrice)
select prod2
};
foreach (var prodObj in highPriceQuery)
{
Console.WriteLine(prodObj.Key);
foreach (var listing in prodObj.MostExpensiveProducts)
{
Console.WriteLine(listing.ProductName);
}
}
Northwind サンプル データベースに対して前のクエリを実行すると、結果は次のようになります。
1
Côte de Blaye
2
Vegie-spread
3
Sir Rodney's Marmalade
4
Raclette Courdavault
5
Gnocchi di nonna Alice
6
Thüringer Rostbratwurst
7
Manjimup Dried Apples
8
Carnarvon Tigers
参照
概念
サンプル データベースのダウンロード (LINQ to SQL)