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 has two columns ID and Name   I tried something like the following  but it didn t work   var movies    db Movies OrderBy  m   gt    m CategoryID  m Name

User · Accepted Answer

This should work for you   var movies    db Movies OrderBy c   gt  c Category  ThenBy n   gt  n Name

User · Answer

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

User · Answer

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 lt Movie gt        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   gt  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

User · Answer

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   gt  i Property1  SmartOrderByDescending i   gt  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 lt T gt  this IQueryable lt T gt  queryable            if queryable    null                throw new ArgumentNullException  queryable                       return queryable Expression Type    typeof IOrderedQueryable lt T gt               public static IQueryable lt T gt  SmartOrderBy lt T  TKey gt  this IQueryable lt T gt  queryable  Expression lt Func lt T  TKey gt  gt  keySelector            if queryable IsOrdered                  var orderedQuery   queryable as IOrderedQueryable lt T gt               return orderedQuery ThenBy keySelector             else               return queryable OrderBy keySelector                        public static IQueryable lt T gt  SmartOrderByDescending lt T  TKey gt  this IQueryable lt T gt  queryable  Expression lt Func lt T  TKey gt  gt  keySelector            if queryable IsOrdered                  var orderedQuery   queryable as IOrderedQueryable lt T gt               return orderedQuery ThenByDescending keySelector             else               return queryable OrderByDescending keySelector

User · Answer

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   gt  m CategoryID  ThenBy m   gt  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   gt  m CategoryID  OrderBy m   gt  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

User · Answer

Add  new    var movies    db Movies OrderBy  m   gt  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   gt   m CategoryID ToString     m Name

User · Answer

If use generic repository   gt  lstModule    ModuleRepository GetAll   OrderBy x   gt  new   x Level   gt  x Rank   ToList      else   gt   db Module Where x  gt          OrderBy x   gt  new   x Level  x Rank   ToList

User · Answer

If use generic repository   gt  lstModule    ModuleRepository GetAll   OrderBy x   gt  new   x Level   gt  x Rank   ToList      else   gt   db Module Where x  gt          OrderBy x   gt  new   x Level  x Rank   ToList

User · Answer

Add  new    var movies    db Movies OrderBy  m   gt  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   gt   m CategoryID ToString     m Name

User · Answer

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   gt  m CategoryID  ThenBy m   gt  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   gt  m CategoryID  OrderBy m   gt  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

User · Answer

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   gt  i Property1  SmartOrderByDescending i   gt  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 lt T gt  this IQueryable lt T gt  queryable            if queryable    null                throw new ArgumentNullException  queryable                       return queryable Expression Type    typeof IOrderedQueryable lt T gt               public static IQueryable lt T gt  SmartOrderBy lt T  TKey gt  this IQueryable lt T gt  queryable  Expression lt Func lt T  TKey gt  gt  keySelector            if queryable IsOrdered                  var orderedQuery   queryable as IOrderedQueryable lt T gt               return orderedQuery ThenBy keySelector             else               return queryable OrderBy keySelector                        public static IQueryable lt T gt  SmartOrderByDescending lt T  TKey gt  this IQueryable lt T gt  queryable  Expression lt Func lt T  TKey gt  gt  keySelector            if queryable IsOrdered                  var orderedQuery   queryable as IOrderedQueryable lt T gt               return orderedQuery ThenByDescending keySelector             else               return queryable OrderByDescending keySelector

User · Answer

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

User · Answer

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 lt Movie gt        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   gt  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

[linq] Multiple "order by" in LINQ

Examples related to linq

Examples related to sql-order-by