Linq-to-Entities Dynamic sorting

Question

This is my query, how can I use string as orderby parameter?

string sortColumn="Title";

var  items = (from ltem in ctxModel.Items
              where ltem.ItemID == vId
              orderby //something here
              select ltem).Skip(PageSize * PageIndex).Take(PageSize);

UPDATE:
I can't just OrderBy the result set, because I FIRST need to sort, and only THEN to page.

Answer 1

I use this helper:

public static class OrderExt
{
    private static IOrderedQueryable<T> Order<T>(this IQueryable<T> source, string propertyName, SortDirection descending, bool anotherLevel = false)
    {
        var param = Expression.Parameter(typeof(T), string.Empty);
        var property = Expression.PropertyOrField(param, propertyName);
        var sort = Expression.Lambda(property, param);

        var call = Expression.Call(
            typeof (Queryable),
            (!anotherLevel ? "OrderBy" : "ThenBy") +
            (descending == SortDirection.Descending ? "Descending" : string.Empty),
            new[] {typeof (T), property.Type},
            source.Expression,
            Expression.Quote(sort));

        return (IOrderedQueryable<T>)source.Provider.CreateQuery<T>(call);
    }
}

to call the helper, eg do this:

string sort = HttpContext.Current.Request.QueryString["sort"];
var products = _productRepository.OrderBy(sort, SortDirection.Ascending);

Answer 2

Here's another alternative, the EntitySorter. Allows a bit what dynamic LINQ does with strings, but wraps the operation in a object, just like with the Query Object pattern. It allows both sorting by strings, and by type safe constructs. Here are some examples:

// Ways of defining an entity sorter
// 1. Using strings:
IEntitySorter<Person> sorter = EntitySorter<Person>
    .OrderBy("Address.City")
    .ThenByDescending("Id");

// 2. Defining a sorter with lambda's
IEntitySorter<Person> sorter = EntitySorter<Person>
    .OrderByDescending(p => p.Name)
    .ThenBy(p => p.Id)
    .ThenByDescending(p => p.Address.City);

// 3. Using a LINQ query
IEntitySorter<Person> sorter =
    from person in EntitySorter<Person>.AsQueryable()
    orderby person.Name descending, person.Address.City
    select person;

// And you can pass a sorter from your presentation layer
// to your business layer, and you business layer may look
// like this:
static Person[] GetAllPersons(IEntitySorter<Person> sorter)
{
    using (var db = ContextFactory.CreateContext())
    {
        IOrderedQueryable<Person> sortedList =
            sorter.Sort(db.Persons);

        return sortedList.ToArray();
    }
}

You can find the code here.

Answer 3

Others have suggested using Dynamic link or other libraries. Personally, I would not bring in a library dependency for such a small task. But two other paths that you can take are...

• Use Object Call syntax and build your query expression tree dynamically. For example...

See http://blog.cincura.net/229310-sorting-in-iqueryable-using-string-as-column-name/

It is important to consider Deferred Execution in this scenario. You can safely build your query that returns an IQueryable object and then run a object query sort on that object. Your query will only be run once, when the data is actually accessed.

The above blog post is an example of how you can use the Expression API to build and expression tree that you can use for your OrderBy. It really just sounds complicated. The MSDN article may be a better reference. See How to: Use Expression Trees to Build Dynamic Queries on MSDN.

Or

• Use the simple route and just use a switch on the title for the entire query.

Eg.

ItemType items = default(ItemType);
switch(sortColumn)
{
     case "Title":
     {
           items = ctxModel.Items
                    .Where(i => i.ItemID == vId)
                    .OrderBy( i => i.Title);
     }
     break;
 }