Try .NET /elinq/
Powered by Try .NET

SQL Server ORDER BY

A) Sort a result set by one column in ascending order

There is a full integration between ELINQ and EF, so we can sort using Linq. This is usually preferrable since we usually want to sort the outer result set managed by EF (run and see the SQL to understand better).

var query = DbContext.Customers.Query((Customers customer) =>
{
    var result = SELECT(customer);
    FROM(customer);

    return result;
})
                .OrderBy(c => c.FirstName);

foreach (var customer in query.Take(3))
    Console.WriteLine((customer.FirstName, customer.LastName));

B) Sort a result set by one column in descending order

It's convenient to return the "full" entities in ORM because otherwise there will be an "object hell". We will behave this way throughput this tutorial whenever it makes sense.

var query = DbContext.Customers.Query((Customers customer) =>
{
    var result = SELECT(customer);
    FROM(customer);

    return result;
})
                .OrderByDescending(c => c.FirstName);

foreach (var customer in query.Take(3))
    Console.WriteLine((customer.FirstName, customer.LastName));

C) Sort a result set by multiple columns

var query = DbContext.Customers.Query((Customers customer) =>
{
    var result = SELECT(customer);
    FROM(customer);

    return result;
})
                .OrderByDescending(c => c.City)
                .ThenBy(c => c.FirstName);

foreach (var customer in query.Take(3))
    Console.WriteLine((customer.FirstName, customer.LastName));

D) Sort a result set by multiple columns and different orders

var query = DbContext.Customers.Query((Customers customer) =>
{
    var result = SELECT(customer);
    FROM(customer);

    return result;
})
                .OrderByDescending(c => c.City)
                .ThenBy(c => c.FirstName);

foreach (var customer in query.Take(3))
    Console.WriteLine((customer.FirstName, customer.LastName));

E) Sort a result set by a column that is not in the select list

var query = DbContext.Customers.Query((Customers customer) =>
{
    var result = SELECT(customer);
    FROM(customer);

    return result;
})
                .OrderBy(c => c.State);

foreach (var customer in query.Take(3))
    Console.WriteLine((customer.FirstName, customer.LastName));

F) Sort a result set by an expression

ELINQ maps String's Length() function to TSQL's LEN.

var query = DbContext.Customers.Query((Customers customer) =>
{
    var result = SELECT(customer);
    FROM(customer);
    ORDER(BY(customer.FirstName.Length).DESC);
    OFFSET(0).ROWS();

    return result;
});

foreach (var customer in query.Take(3))
    Console.WriteLine((customer.FirstName, customer.LastName));

But we can do it in Linq as previously:

var query = DbContext.Customers.Query((Customers customer) =>
{
    var result = SELECT(customer);
    FROM(customer);

    return result;
})
                .OrderByDescending(c => c.FirstName.Length);

foreach (var customer in query.Take(3))
    Console.WriteLine((customer.FirstName, customer.LastName));

G) Sort by ordinal positions of columns

ELINQ can do this, but it's definitely not a recommended way to sort:

var query = DbContext.Set<FullName>()
                .Query((Customers customer, FullName alias) =>
                {
                    var result = SELECT<FullName>(customer.FirstName.@as(alias.FirstName), customer.LastName.@as(alias.LastName));
                    FROM(customer);
                    ORDER(BY(1), BY(2));
                    OFFSET(0).ROWS();

                    return result;
                });

foreach (var customer in query.Take(3))
    Console.WriteLine((customer.FirstName, customer.LastName));

< BACK | HOME