Entity Framework dynamic sorting and pagination

Suppose you need to paginate and letting the user choose the sort field in entity framework, lets see how you can accomplish this.

In EntityFramework you can query with E-SQL both building a query in string format, but also using standard method syntax, here is an example (based on NorthWind)

1
2
3

var query = context.Customers
           .OrderBy("it.ContactTitle"); 
query.Dump();

This work perfectly, you can sort dynamically only specifying the property you want to use for sorting. Adding pagination is a breeze

1
2
3
4

var query = context.Customers
           .OrderBy("it.ContactTitle")
           .Skip(20).Take(5);
query.Dump();

This query is really interesting, because you are actually using e-SQL for the orderby part, then you resort to standard linq operator. Now you can be surprised because this version does not work:

1
2
3
4

var query = context.Customers
           .Skip(20).Take(5)
           .OrderBy("it.ContactTitle");
query.Dump();

After all you simply revert the order of methods, but there is a general rule that states when you use a LINQ operator you cannot use e-SQL operator anymore, this is why this second snippet does not work. All the methods that accepts entity property name with the syntax it.propertyname belongs to the E-SQL syntax, they are used to dynamically build  an ESQL query. Now suppose you want to specify a condition. You can try this

1
2
3
4

var query = context.Customers
           .OrderBy("it.ContactTitle")
           .Where(c => c.CustomerID.Contains("m"))
           .Skip(5).Take(5);

But it does not work, because it tells you The method ‘Skip’ is only supported for sorted input in LINQ to Entities. The method ‘OrderBy’ must be called before the method ‘Skip’. This happens because EF examines the E-SQL part, it order by ContactTitle, then pass everything to the LINQ part that starts with where methdo, the Where method gets passed the E-SQL query, then you call Skip, and now Skip operator looks in the expression tree for a Sort operation, it does not found it because it only found a Where, so it throws an exception. You could be tempted to move the Skip part before the where

1
2
3
4

var query = context.Customers
           .OrderBy("it.ContactName")
           .Skip(5).Take(5)
           .Where(c => c.CustomerID.Contains("m"));

But this does not work because pagination occurs before the filtering so it does not return the result you want. A possible solution is directly using E-SQL.

1
2
3
4
5
6
7
8
9

  var queryESQL = @"select VALUE Customers from Customers
                     where Customers.ContactName like '%M%'
                   order by Customers.ContactName
                   skip @s limit @l";
  var query = context.CreateQuery<ModelToTest.Customers>(
          queryESQL, 
        new ObjectParameter("s", 5) ,
        new ObjectParameter("l", 5));
  query.Dump();

This permits you to build the query as string, so you can specify ordering with no problem. If you still like method query (as I like) you need to be sure that the orderby is just before the skip.

1
2
3
4
5

var query = context.Customers
           .Where("it.ContactName like '%m%'")
           .OrderBy("it.ContactName")
           .Skip(5).Take(5);
query.Dump();

Or you can use the Skip method belonging to ESQL syntax

1
2
3
4
5

var query = context.Customers
           .Where("it.ContactName like '%m%'")
           .Skip("it.ContactName", "5")
           .Take(5);
query.Dump();

alk.

Tags: .NET Framework Entity Framework Pagination