Multiple "order by" in LINQ

Asked 2023-09-21 08:11:13 View 251,812

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

The movie table has three columns ID, Name and CategoryID. The category table has two 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 })
  • Here is why this can't work: The lambda expression in the parentheses is supposed to return a value which can be used to order the items: m.CategoryID is a number which can be used to order the items. But "m.CategoryID, m.Name" doesn't make sense in this context. - anyone
  • .ThenBy is what you're searching? - anyone
  • If by any chance you want to sort them in descending order there here is the way to go. - anyone

Answers

This should work for you:

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

Answered   2023-09-21 08:11:13

  • Thanks for the answer of course... But instead of Var movies = _db.Movies.Orderby(c => c.Category).ThenBy(n => n.Name) if I use Var movies = _db.Movies.Orderby(c => c.Category).OrderBy(n => n.Name) 2 times "orderBy" why is the result different? - anyone
  • @devendra, result is different because second "OrderBy" works over the collection which is result of first "OrderBy" and reorder its items - anyone
  • ThenBy operates on IOrderedEnumerable (which is returned by OrderBy) - anyone
  • Please note: .ThenBy() = Ascending, and .ThenByDescending() = Descending!! (sounds logical, huh?) - anyone
  • IQueryable<class> does not contain a definition for ThenBy - anyone

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;

Answered   2023-09-21 08:11:13

  • There's not a way to flip back and forth between descending and non in this syntax is there? - anyone
  • Actually, your answer is the equivalent to _db.Movies.Orderby(c => c.Category).OrderBy(n => n.Name). More correct is from row in _db.Movies orderby row.Category descending orderby row.Name select row - anyone
  • @Lodewijk: I believe you have that exactly backwards. Your example will end up having row.Name being the primary column and row.Category secondary, which is the equivalent of _db.Movies.Orderby(c => c.Category).OrderBy(n => n.Name). The two snippets you provide are equivalent to each other, not to the OP's. - anyone
  • The only downside to using the SQL syntax for Linq is that not all of the functions are supported, most but not all - anyone

Add "new":

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

That works on my box. It does return something that can be used to sort. It returns an object with two values.

Similar, but different to sorting by a combined column, as follows.

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

Answered   2023-09-21 08:11:13

  • Be careful when using that for numbers. - anyone
  • You can use OrderByDescending and ThenBy, or OrderBy and ThenByDescending, depending upon your need. - anyone
  • I'm pretty sure that .OrderBy( m => new { m.CategoryID, m.Name }) and .OrderBy( m => new { m.Name, m.CategoryID }) will produce the same results rather than respecting the intended priority. It will sometimes appear to give you the ordering you want purely by coincidence. Additionally m.CategoryID.ToString() + m.Name will produce incorrect orderings if CategoryID is an int. For example, something with id=123, name=5times will appear after id=1234, name=something instead of before. It's also not inefficient to do string comparisons where int comparisons could occur. - anyone
  • When I try to order by on an anonymous type, I get an ArgumentException with the message "At least one object must implement IComparable.". I see others having to declare a comparer when doing this. See stackoverflow.com/questions/10356864/… . - anyone
  • This is absolutely wrong. Ordering by a new anonymous type that has no ICompariable implementation cannot work, because there is not order to the properties of an anonymous type. It wouldn't know whether to sort on CategoryID first or Name first, let alone if they were to be sorted in opposite orders. - anyone

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

Answered   2023-09-21 08:11:13

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

Answered   2023-09-21 08:11:13

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.

Answered   2023-09-21 08:11:13

  • I like this as being more general than thenby since you can do weird things with the compare including having different compare objects with different algorithms ready to go. This is better than my preferred solution before learning about thenby which was to create a class that implements the IComparable interface. - anyone
  • Since 2012 (.NET version 4.5) you do not have to create a class MovieComparer yourself; instead you can do _db.Movies.OrderBy(item => item, Comparer<Movie>.Create((x, y) => { if (x.CategoryId == y.CategoryId) { return x.Name.CompareTo(y.Name); } else { return x.CategoryId.CompareTo(y.CategoryId); } }));. Of course, if you prefer to write the logic as one expression, instead of if...else, then the lamda (x, y) => expr can be simpler. - anyone

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

Answered   2023-09-21 08:11:13

  • Anonymous expressions will be parsed locally by entity framework core. The LINQ expression could not be translated and will be evaluated locally. - anyone