Thursday, May 31, 2012

Dynamic LINQ OrderBy


I found an example in the VS2008 Examples for Dynamic LINQ that allows you to use a sql-like string (e.g. OrderBy("Name, Age DESC")) for ordering. Unfortunately, the method included only works on IQueryable<T>. Is there any way to get this functionality on IEnumerable<T>?



Source: Tips4all

13 comments:

  1. Just stumbled into this oldie...

    To do this without the dynamic LINQ library, you just need the code as below. This covers most common scenarios including nested properties.

    To get it working with IEnumerable<T> you could add some wrapper methods that go via AsQueryable - but the code below is the core Expression logic needed.

    public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string property)
    {
    return ApplyOrder<T>(source, property, "OrderBy");
    }
    public static IOrderedQueryable<T> OrderByDescending<T>(this IQueryable<T> source, string property)
    {
    return ApplyOrder<T>(source, property, "OrderByDescending");
    }
    public static IOrderedQueryable<T> ThenBy<T>(this IOrderedQueryable<T> source, string property)
    {
    return ApplyOrder<T>(source, property, "ThenBy");
    }
    public static IOrderedQueryable<T> ThenByDescending<T>(this IOrderedQueryable<T> source, string property)
    {
    return ApplyOrder<T>(source, property, "ThenByDescending");
    }
    static IOrderedQueryable<T> ApplyOrder<T>(IQueryable<T> source, string property, string methodName) {
    string[] props = property.Split('.');
    Type type = typeof(T);
    ParameterExpression arg = Expression.Parameter(type, "x");
    Expression expr = arg;
    foreach(string prop in props) {
    // use reflection (not ComponentModel) to mirror LINQ
    PropertyInfo pi = type.GetProperty(prop);
    expr = Expression.Property(expr, pi);
    type = pi.PropertyType;
    }
    Type delegateType = typeof(Func<,>).MakeGenericType(typeof(T), type);
    LambdaExpression lambda = Expression.Lambda(delegateType, expr, arg);

    object result = typeof(Queryable).GetMethods().Single(
    method => method.Name == methodName
    && method.IsGenericMethodDefinition
    && method.GetGenericArguments().Length == 2
    && method.GetParameters().Length == 2)
    .MakeGenericMethod(typeof(T), type)
    .Invoke(null, new object[] {source, lambda});
    return (IOrderedQueryable<T>)result;
    }

    ReplyDelete
  2. I found the answer. I can use the .AsQueryable<>() extension method to convert my list to IQueryable, then run the dynamic order by against it.

    ReplyDelete
  3. Just stumbled across this question.

    Using Marc's ApplyOrder implementation from above, I slapped together an Extension method that handles SQL-like strings like:

    list.OrderBy("MyProperty DESC, MyOtherProperty ASC");


    Details can be found here: http://aonnull.blogspot.com/2010/08/dynamic-sql-like-linq-orderby-extension.html

    ReplyDelete
  4. I guess it would work to use reflection to get whatever property you want to sort on:

    IEnumerable<T> myEnumerables
    var query=from enumerable in myenumerables
    where some criteria
    orderby GetPropertyValue(enumerable,"SomeProperty")
    select enumerable

    private static object GetPropertyValue(object obj, string property)
    {
    System.Reflection.PropertyInfo propertyInfo=obj.GetType().GetProperty(property);
    return propertyInfo.GetValue(obj, null);
    }


    Note that using reflection is considerably slower than accessing the property directly, so the performance would have to be investigated.

    ReplyDelete
  5. Just building on what others have said. I found that the following works quite well.

    public static IEnumerable<T> OrderBy<T>(this IEnumerable<T> input, string queryString)
    {
    if (string.IsNullOrEmpty(queryString))
    return input;

    int i = 0;
    foreach (string propname in queryString.Split(','))
    {
    var subContent = propname.Split('|');
    if (Convert.ToInt32(subContent[1].Trim()) == 0)
    {
    if (i == 0)
    input = input.OrderBy(x => GetPropertyValue(x, subContent[0].Trim()));
    else
    input = ((IOrderedEnumerable<T>)input).ThenBy(x => GetPropertyValue(x, subContent[0].Trim()));
    }
    else
    {
    if (i == 0)
    input = input.OrderByDescending(x => GetPropertyValue(x, subContent[0].Trim()));
    else
    input = ((IOrderedEnumerable<T>)input).ThenByDescending(x => GetPropertyValue(x, subContent[0].Trim()));
    }
    i++;
    }

    return input;
    }

    ReplyDelete
  6. You could add it:

    public static IEnumerable<T> OrderBy( this IEnumerable<T> input, string queryString) {
    //parse the string into property names
    //Use reflection to get and sort by properties
    //something like

    foreach( string propname in queryString.Split(','))
    input.OrderBy( x => GetPropertyValue( x, propname ) );

    // I used Kjetil Watnedal's reflection example
    }


    The GetPropertyValue function is from Kjetil Watnedal's answer

    The issue would be why? Any such sort would throw exceptions at run-time, rather than compile time (like D2VIANT's answer).

    If you're dealing with Linq to Sql and the orderby is an expression tree it will be converted into SQL for execution anyway.

    ReplyDelete
  7. An alternate solution uses the following class/interface. It's not truly dynamic, but it works.

    public interface IID
    {
    int ID
    {
    get; set;
    }
    }

    public static class Utils
    {
    public static int GetID<T>(ObjectQuery<T> items) where T:EntityObject, IID
    {
    if (items.Count() == 0) return 1;
    return items.OrderByDescending(u => u.ID).FirstOrDefault().ID + 1;
    }
    }

    ReplyDelete
  8. I've stumble this question looking for Linq multiple orderby clauses
    and maybe this was what the author was looking for

    Here's how to do that:

    IEnumerable query = pets.OrderBy(pet => pet.Name).ThenByDescending(pet => pet.Age);

    ReplyDelete
  9. Here's something else I found interesting.
    If your source is a DataTable, you can use dynamic sorting without using Dynamic Linq

    DataTable orders = dataSet.Tables["SalesOrderHeader"];
    EnumerableRowCollection<DataRow> query = from order in orders.AsEnumerable()
    orderby order.Field<DateTime>("OrderDate")
    select order;
    DataView view = query.AsDataView();
    bindingSource1.DataSource = view;


    reference: http://msdn.microsoft.com/en-us/library/bb669083.aspx (Using DataSetExtensions)

    Here is one more way to do it by converting it to a DataView:

    DataTable contacts = dataSet.Tables["Contact"];
    DataView view = contacts.AsDataView();
    view.Sort = "LastName desc, FirstName asc";
    bindingSource1.DataSource = view;
    dataGridView1.AutoResizeColumns();

    ReplyDelete
  10. Too easy without any complication :

    1- Add using System.Linq.Dynamic; at the top.

    2- Use vehicles = vehicles.AsQueryable().OrderBy("Make ASC, Year DESC").ToList();

    ReplyDelete
  11. I was trying to do this but having problems with Kjetil Watnedal's solution because I don't use the inline linq syntax - I prefer method-style syntax. My specific problem was in trying to do dynamic sorting using a custom IComparer.

    My solution ended up like this:

    Given an IQueryable query like so:

    List<DATA__Security__Team> teams = TeamManager.GetTeams();
    var query = teams.Where(team => team.ID < 10).AsQueryable();


    And given a run-time sort field argument:

    string SortField; // Set at run-time to "Name"


    The dynamic OrderBy looks like so:

    query = query.OrderBy(item => item.GetReflectedPropertyValue(SortField));


    And that's using a little helper method called GetReflectedPropertyValue():

    public static string GetReflectedPropertyValue(this object subject, string field)
    {
    object reflectedValue = subject.GetType().GetProperty(field).GetValue(subject, null);
    return reflectedValue != null ? reflectedValue.ToString() : "";
    }




    One last thing - I mentioned that I wanted the OrderBy to use custom IComparer - because I wanted to do Natural sorting.

    To do that, I just alter the OrderBy to:

    query = query.OrderBy(item => item.GetReflectedPropertyValue(SortField), new NaturalSortComparer<string>());


    See this post for the code for NaturalSortComparer().

    ReplyDelete
  12. See if this helps in any way

    http://www.onedotnetway.com/dynamic-sort-with-linq-to-sql/

    ReplyDelete
  13. Is this what you're thinking of?


    public static void OrderByExample()
    {
    Pet[] pets = { new Pet { Name="Barley", Age=8 },
    new Pet { Name="Boots", Age=4 },
    new Pet { Name="Whiskers", Age=1 } };

    IEnumerable query = pets.OrderBy(pet => pet.Age);

    foreach (Pet pet in query)
    {
    Console.WriteLine("{0} - {1}", pet.Name, pet.Age);
    }
    }


    Source: http://msdn.microsoft.com/en-us/library/bb534966.aspx

    ReplyDelete