NeuroAgent

Complete Guide: Multiple OrderBy in LINQ

Master LINQ multi-column sorting with ThenBy and tuple syntax. Learn how to sort data by multiple criteria efficiently with practical examples and best practices.

#csharp-linq#database-querying#entity-framework#linq-sorting#multi-column-sort#thenby-method#tuple-syntax
Question

How to use multiple “order by” clauses in LINQ to sort data by multiple criteria?

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 the following LINQ query, but it doesn’t work:

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

What is the correct syntax for sorting data by multiple columns in LINQ?

NeuroAgent

You can sort data by multiple columns in LINQ using either the ThenBy method or tuple syntax. The correct approach for your scenario would be:

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

Or using tuple syntax:

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

Contents

Basic Multi-Column Sorting Syntax

The most common and readable approach for multi-column sorting in LINQ is using the ThenBy method after an initial OrderBy. This creates a stable sorting where the first sort criterion (primary key) determines the main order, and subsequent ThenBy calls provide secondary, tertiary, etc. sorting criteria.

csharp
// Primary sort by CategoryID, secondary sort by Name
var movies = _db.Movies
    .OrderBy(m => m.CategoryID)
    .ThenBy(m => m.Name);

This approach creates a compound sort key that effectively sorts the collection first by CategoryID, and for movies with the same CategoryID, sorts them by Name.

Key Insight: You can only use OrderBy once per query. All subsequent sorting must use ThenBy or ThenByDescending. Using multiple OrderBy calls will overwrite previous sorting.

ThenBy vs ThenByDescending

You can mix ascending and descending sorting by using ThenByDescending for subsequent columns:

csharp
var movies = _db.Movies
    .OrderBy(m => m.CategoryID)          // Ascending by CategoryID
    .ThenByDescending(m => m.Name);     // Descending by Name within each category

The pattern is:

  • First sort: OrderBy or OrderByDescending
  • Secondary sorts: ThenBy or ThenByDescending
csharp
// Complex example with mixed sort directions
var movies = _db.Movies
    .OrderByDescending(m => m.CategoryID)  // Highest CategoryID first
    .ThenBy(m => m.Name)                  // Alphabetical Name within each category
    .ThenByDescending(m => m.ReleaseDate); // Most recent movies first

Tuple Syntax for Multi-Column Sorting

An alternative approach is using tuple syntax, which can be more concise for simple multi-column sorts:

csharp
// Sort by both CategoryID and Name in ascending order
var movies = _db.Movies
    .OrderBy(m => new { m.CategoryID, m.Name });

The tuple approach automatically sorts by all properties in the order they appear in the object initializer. This is equivalent to:

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

Key Differences:

  • Tuple syntax: More concise, all columns have the same sort direction
  • ThenBy syntax: More readable, allows mixed sort directions, explicit about sorting order

Sorting with Joined Tables

If you want to sort by category name instead of category ID, you’ll need to join the tables first:

csharp
var movies = _db.Movies
    .Join(_db.Categories, 
          m => m.CategoryID, 
          c => c.ID, 
          (m, c) => new { Movie = m, Category = c })
    .OrderBy(x => x.Category.Name)      // Sort by category name
    .ThenBy(x => x.Movie.Name);         // Then by movie name

Or using method chaining:

csharp
var movies = _db.Movies
    .Join(_db.Categories, 
          m => m.CategoryID, 
          c => c.ID, 
          (m, c) => new { m, c })
    .OrderBy(x => x.c.Name)
    .ThenBy(x => x.m.Name);

If you want the result as movie objects, you can select them at the end:

csharp
var movies = _db.Movies
    .Join(_db.Categories, 
          m => m.CategoryID, 
          c => c.ID, 
          (m, c) => new { m, c })
    .OrderBy(x => x.c.Name)
    .ThenBy(x => x.m.Name)
    .Select(x => x.m);  // Return only the movie objects

Common Mistakes and Pitfalls

Incorrect Multiple OrderBy Usage

csharp
// WRONG - This will only sort by Name, ignoring CategoryID
var movies = _db.Movies.OrderBy(m => m.CategoryID).OrderBy(m => m.Name);

Incorrect Syntax in Lambda Expression

csharp
// WRONG - Invalid syntax
var movies = _db.Movies.OrderBy(m => { m.CategoryID, m.Name });

ThenBy Without OrderBy

csharp
// WRONG - Compile error: OrderBy required before ThenBy
var movies = _db.Movies.ThenBy(m => m.Name);

Using Object Initializer Instead of Properties

csharp
// WRONG - This creates a single sort key, not multiple columns
var movies = _db.Movies.OrderBy(m => new Movie { CategoryID = m.CategoryID, Name = m.Name });

Performance Considerations

When working with large datasets, consider these performance aspects:

  1. Database vs. In-Memory Sorting: When using LINQ to SQL/Entity Framework, the sorting happens in the database if possible, which is more efficient.

  2. Index Usage: Ensure your database tables have appropriate indexes on the columns you’re sorting by for optimal performance.

  3. Complex Sort Keys: Tuple sorting creates more complex expressions that may not be as efficiently optimized by the database.

  4. Memory Usage: For in-memory collections (LINQ to Objects), complex sorting can be memory-intensive.

csharp
// Database-optimized approach (LINQ to SQL/EF)
var movies = _db.Movies
    .OrderBy(m => m.CategoryID)
    .ThenBy(m => m.Name)
    .ToList();  // Sorting happens in database
csharp
// In-memory approach (LINQ to Objects)
var movies = _db.Movies.ToList()  // Load all data first
    .OrderBy(m => m.CategoryID)
    .ThenBy(m => m.Name);  // Sorting happens in application memory

Practical Examples

Example 1: Basic Multi-Column Sort

csharp
// Sort movies by category ID, then by name
var sortedMovies = _db.Movies
    .OrderBy(m => m.CategoryID)
    .ThenBy(m => m.Name)
    .ToList();

Example 2: Mixed Sort Directions

csharp
// Sort by category (ascending), then by release date (newest first)
var sortedMovies = _db.Movies
    .OrderBy(m => m.CategoryID)
    .ThenByDescending(m => m.ReleaseDate)
    .ToList();

Example 3: Sorting with Navigation Properties

If you have navigation properties set up:

csharp
// Assuming Movie has a Category navigation property
var sortedMovies = _db.Movies
    .OrderBy(m => m.Category.Name)      // Sort by category name
    .ThenBy(m => m.Name)                // Then by movie name
    .ToList();

Example 4: Dynamic Multi-Column Sorting

For scenarios where sort criteria might vary:

csharp
// Sort by category ID, then by name (case-insensitive)
var sortedMovies = _db.Movies
    .OrderBy(m => m.CategoryID)
    .ThenBy(m => m.Name.ToLower())
    .ToList();

Example 5: Pagination with Multi-Column Sorting

csharp
int pageIndex = 1;
int pageSize = 10;

var pagedMovies = _db.Movies
    .OrderBy(m => m.CategoryID)
    .ThenBy(m => m.Name)
    .Skip((pageIndex - 1) * pageSize)
    .Take(pageSize)
    .ToList();

Conclusion

To effectively sort data by multiple columns in LINQ:

  1. Use ThenBy after OrderBy for the most readable and flexible approach
  2. Consider tuple syntax for simpler multi-column sorts where all columns sort in the same direction
  3. Remember you can only use OrderBy once per query - all subsequent sorting uses ThenBy
  4. Mix ThenBy and ThenByDescending to achieve different sort directions for different columns
  5. Join tables first when sorting by related table columns
  6. Avoid common mistakes like multiple OrderBy calls or incorrect lambda syntax

The correct syntax for your specific requirement is:

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

This will give you movies sorted first by CategoryID, and then alphabetically by Name within each category.

Sources

  1. Microsoft Docs - LINQ Query Syntax vs. Method Syntax
  2. Microsoft Docs - Ordering Data (LINQ)
  3. Stack Overflow - Multiple OrderBy in LINQ
  4. Microsoft Docs - ThenBy Method
How to implement dynamic multi-column sorting in LINQ based on user input?What are the performance differences between ThenBy and tuple syntax in LINQ?How to handle null values when sorting multiple columns in LINQ?Can I use LINQ to sort by multiple columns with different sort directions?How to optimize LINQ queries with multiple OrderBy clauses for large datasets?What are the common performance pitfalls when using multi-column sorting in LINQ?
Ask NeuroAgent