Sort Elements in a Sequence

 

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

System_CAPS_ICON_note.jpg 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.

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

Query Examples
Downloading Sample Databases

Show: