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;
 }

Answer 4

Apparently the other allusions to Dynamic Linq are not clear enough. Let me shed some light..

Using Dynamic Linq does not necessarily indicate the need for an assembly dependency.

Dynamic Linq is contained in one source file, if I am not mistaken, that is included in the C# samples everyone should have at least looked at sometime in the last 3 years, and can easily be dropped into a project and namespaced to prevent collisions, thus providing expression building services that can be utilized wherever this need arises.

I consider the ability to safely construct an expression from a reasonably arbitrary string, which can easily be built on the fly to be the best example of 'dynamic'.

Consider:

    var query = northwind.Products
                         .Where("CategoryID = 3 AND UnitPrice > 3")
                         .OrderBy("SupplierID");

Answer 5

That query looks like you're using custom databinding and/or ObjectDataSource, regardless, there is a way to do this using an extension method which takes a sort expression and dynamically appends an OrderBy() call (expression) to the linq query. I documented how in blog post a while back, which coincidentally was part of this SO question. If you need more than that, you could use dynamic linq which is documented pretty well by scottgu.

EDIT: using the extension method would make it look like

string sortColumn="Title";

    var  items = (from ltem in ctxModel.Items
                  where ltem.ItemID == vId
                  select ltem).Skip(PageSize * PageIndex).Take(PageSize).OrderBy(sortColumn);