Sort Elements in a Sequence
Use the OrderBy operator to sort a sequence according to one or more keys.
Note
LINQ to SQL is designed to support ordering by simple primitive types, such as string, int, and so on. It does not support ordering for complex multi-valued classes, such as anonymous types. It also does not support byte datatypes.
Example 1
The following example sorts Employees by date of hire.
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);
}
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
Example 2
The following example uses where to sort Orders shipped to London by freight.
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);
}
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
Example 3
The following example sorts Products by unit price from highest to lowest.
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);
}
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
Example 4
The following example uses a compound OrderBy to sort Customers by city and then by contact name.
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);
}
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
Example 5
The following example sorts Orders from EmployeeID 1 by ShipCountry, and then by highest to lowest freight.
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);
}
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
Example 6
The following example combines OrderBy, Max, and GroupBy operators to find the Products that have the highest unit price in each category, and then sorts the group by category id.
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);
}
}
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
If you run the previous query against the Northwind sample database, the results will resemble the following:
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