Multiple “order by” in LINQ

Question

I have two tables, movies and categories, and I get an ordered list by categoryID first and then by Name.

The movie table has three columns, ID, Name, and CategoryID. The category table two has columns, ID, and Name.

I tried something like the following, but it didn't work.

var movies = _db.Movies.OrderBy( m => { m.CategoryID, m.Name })

Answer 1

I have created some extension methods (below) so you don't have to worry if an IQueryable is already ordered or not. If you want to order by multiple properties just do it as follows:

// We do not have to care if the queryable is already sorted or not. 
// The order of the Smart* calls defines the order priority
queryable.SmartOrderBy(i => i.Property1).SmartOrderByDescending(i => i.Property2);

This is especially helpful if you create the ordering dynamically, f.e. from a list of properties to sort.

public static class IQueryableExtension
{
    public static bool IsOrdered<T>(this IQueryable<T> queryable) {
        if(queryable == null) {
            throw new ArgumentNullException("queryable");
        }

        return queryable.Expression.Type == typeof(IOrderedQueryable<T>);
    }

    public static IQueryable<T> SmartOrderBy<T, TKey>(this IQueryable<T> queryable, Expression<Func<T, TKey>> keySelector) {
        if(queryable.IsOrdered()) {
            var orderedQuery = queryable as IOrderedQueryable<T>;
            return orderedQuery.ThenBy(keySelector);
        } else {
            return queryable.OrderBy(keySelector);
        }
    }

    public static IQueryable<T> SmartOrderByDescending<T, TKey>(this IQueryable<T> queryable, Expression<Func<T, TKey>> keySelector) {
        if(queryable.IsOrdered()) {
            var orderedQuery = queryable as IOrderedQueryable<T>;
            return orderedQuery.ThenByDescending(keySelector);
        } else {
            return queryable.OrderByDescending(keySelector);
        }
    }
}

Answer 2

This should work for you:

var movies = _db.Movies.OrderBy(c => c.Category).ThenBy(n => n.Name)

Answer 3

use the following line on your DataContext to log the SQL activity on the DataContext to the console - then you can see exactly what your linq statements are requesting from the database:

_db.Log = Console.Out

The following LINQ statements:

var movies = from row in _db.Movies 
             orderby row.CategoryID, row.Name
             select row;

AND

var movies = _db.Movies.OrderBy(m => m.CategoryID).ThenBy(m => m.Name);

produce the following SQL:

SELECT [t0].ID, [t0].[Name], [t0].CategoryID
FROM [dbo].[Movies] as [t0]
ORDER BY [t0].CategoryID, [t0].[Name]

Whereas, repeating an OrderBy in Linq, appears to reverse the resulting SQL output:

var movies = from row in _db.Movies 
             orderby row.CategoryID
             orderby row.Name
             select row;

AND

var movies = _db.Movies.OrderBy(m => m.CategoryID).OrderBy(m => m.Name);

produce the following SQL (Name and CategoryId are switched):

SELECT [t0].ID, [t0].[Name], [t0].CategoryID
FROM [dbo].[Movies] as [t0]
ORDER BY [t0].[Name], [t0].CategoryID

Answer 4

There is at least one more way to do this using LINQ, although not the easiest. You can do it by using the OrberBy() method that uses an IComparer. First you need to implement an IComparer for the Movie class like this:

public class MovieComparer : IComparer<Movie>
{
    public int Compare(Movie x, Movie y)
    {
        if (x.CategoryId == y.CategoryId)
        {
            return x.Name.CompareTo(y.Name);
        }
        else
        {
            return x.CategoryId.CompareTo(y.CategoryId);
        }
    }
}

Then you can order the movies with the following syntax:

var movies = _db.Movies.OrderBy(item => item, new MovieComparer());

If you need to switch the ordering to descending for one of the items just switch the x and y inside the Compare() method of the MovieComparer accordingly.

Answer 5

If use generic repository

> lstModule = _ModuleRepository.GetAll().OrderBy(x => new { x.Level,
> x.Rank}).ToList();

else

> _db.Module.Where(x=> ......).OrderBy(x => new { x.Level, x.Rank}).ToList();

Answer 6

Using non-lambda, query-syntax LINQ, you can do this:

var movies = from row in _db.Movies 
             orderby row.Category, row.Name
             select row;

[EDIT to address comment] To control the sort order, use the keywords ascending (which is the default and therefore not particularly useful) or descending, like so:

var movies = from row in _db.Movies 
             orderby row.Category descending, row.Name
             select row;