LinqToSQL + paging + dynamic sort?

I have a problem on using LinqToSQL with paging + dynamic sorting. These are my sample code.

Using db As New MyDataContext(connectionString)
      db.Log = new DebuggerWritter
      Dim result = db.User.OrderBy(Function(u) u.UserId)

      result = result.Skip((pageNo - 1) * pageSize).Take(pageSize)      
End Using

This is the SQL sc开发者_开发问答ript generated by LINQToSQL, which is only retrieve certain row of records.

SELECT [t1].[UserId], [t1].[UserName]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[UserId]) AS [ROW_NUMBER], [t0].[UserId], [t0].[UserName]
    FROM [dbo].[User] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]

But if i implemented dynamic sort,

Using db As New MyDataContext(connectionString)
      db.Log = new DebuggerWritter
      Dim result = db.User

      For Each s In sortExpressions

          Dim expression As Func(Of User, Object) = Function(u) u.[GetType]().GetProperty(s.propertyName).GetValue(u, Nothing)

          Select Case s.SortOrder
               Case SortExpression.SortDirection.Ascending
                    result = result.OrderBy(expression).AsQueryable
               Case SortExpression.SortDirection.Descending
                    result = result.OrderByDescending(expression).AsQueryable
          End Select
      Next

      result = result.Skip((pageNo - 1) * pageSize).Take(pageSize)  
End Using

This is the SQL script generated this time,

SELECT [t0].[UserId], [t0].[UserName]
FROM [dbo].[User] AS [t0]

How come the paging control script is not generated and the sorting is gone?? Is that the way i implement dynamic sorting is wrong? or LinqToSQL did not support paging + dynamic sorting?? Help!!!

What you're doing in your code will not work. However, I'm a bit surprised that LINQ to SQL doesn't throw an exception.

The OrderBy method that you call in result.OrderBy(expression), is the Enumerable.OrderBy method and not the Queryable.OrderBy method. You already noticed this, because the object it returns is an IEnumerable(Of User) and not an IQueryable(Of User). For this reason you are converting it to an IQueryable(Of User) by calling AsQueryable(). Calling AsQyeryable however, cannot work. The reason for this is that LINQ to SQL processes expression trees (what an IQueryable basically is). And expression trees can be dynamically combined, as you already do with result = result.Skip. Enumerable however, doesn't work with expression trees, but with delegates, which are compiled method calls. While you can convert such a compiled method call to an IQueryable, LINQ to SQL cannot analyze it, because it still is compiled code (it would take introspection to do this, which only specialized tools as Reflector support). In this case LINQ to SQL probably ignored the complete order by part of your query, because it found a (compiled) method call it couldn't process.

So, you're solution will only work when you do the following:

Dim expression As Expression(Of Func(Of User, [ExpectedKeyHere]) = _
   ...  creation of the expression here ...

Select Case s.SortOrder
    Case SortExpression.SortDirection.Ascending
        result = result.OrderBy(expression)
    Case SortExpression.SortDirection.Descending
        result = result.OrderByDescending(expression)
End Select

There is however -yet- another thing. You seem to loop through a set of sortExpressions. While it is possible to append an already sorted collection, an already sorted collection must be called with result.ThenBy or result.ThenByDescending. Calling it with result.OrderBy will completely resort it; you will loose the initial sorting order.

To make a long story short, perhaps you should try to build some sort of EntitySorter object that allows callers of your method to specify the sort order. Your service method could than look like this (sorry, it's C#):

public static Person[] GetAllPersons(IEntitySorter<Person> sorter)
{
    Condition.Requires(sorter, "sorter").IsNotNull();

    using (var db = ContextFactory.CreateContext())
    {
        IOrderedQueryable<Person> sortedList =
            sorter.Sort(db.Persons);

        return sortedList.ToArray();
    }
}

I've written a blog about exactly this, again, it's C#, but I think it will do exactly what you need.

Good luck.