Export (0) Print
Expand All

How to: Sort Elements in a Sequence (LINQ to SQL)

Use the OrderBy operator to sort a sequence according to one or more keys.

NoteNote

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. 

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);
}


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);
}


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);
}


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);
            }



The following example sorts Orders from EmployeeID 1 by ship-to country, 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);
}


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);
    }
}


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

Show:
© 2014 Microsoft