Dynamic Linq to OrderBy Object Nested in IEnumerable

Question

I am trying to write some dynamic linq to order by a property of a list item, for use with NHibernate

public class Company
{
    public string Name { get; set; }
    public List<Employee> Employees { get; set; }
}

public class Employee
{
    public string Name{get; set;}
    public string PayrollNo{get; set;}

}

In this example it would be like to return all Companies and order by PayrollNumber.

The Repository Method would look like this with standard linq.

var companies = session.Query<Company>()
    .OrderBy(x => x.Pieces.FirstOrDefault().PayrollNo)
    .FetchMany(x => x.Employees)

I would like to change this to dynamic linq to order by column headers

 var companies = session.Query<Company>()
    .OrderByName("Employees.PayrollNo"), isDescending)
    .FetchMany(x => x.Employees)

I took an approach similar to the answer in Dynamic LINQ OrderBy on IEnumerable<T> Writing an extension method

But then drilled down with recursion

    public static IQueryable<T> OrderByName<T>(this IQueryable<T> source, string propertyName, Boolean isDescending)
    {
        if (source == null) throw new ArgumentNullException("source");
        if (propertyName == null) throw new ArgumentNullException("propertyName");

        var properties = propertyName.Split('.');
        var type = GetNestedProperty(properties, typeof(T));
        var arg = Expression.Parameter(type.GetProperty(properties.Last()).PropertyType, "x");
        var expr = Expression.Property(arg, properties.Last());

        Type delegateType = typeof(Func<,>).MakeGenericType(typeof(T), type);
        LambdaExpression lambda = Expression.Lambda(delegateType, expr, arg);

        String methodName = isDescending ? "OrderByDescending" : "OrderBy";
        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 (IQueryable<T>)result;
    }


    //Walk the tree of properties looking for the most nested in the string provided
    static Type GetNestedProperty(string[] propertyChain, Type type) 
    {
        if (propertyChain.Count() == 0)
            return type;

        string first = propertyChain.First();
        propertyChain = propertyChain.Skip(1).ToArray(); //strip off first element

        //We hare at the end of the hierarchy
        if (propertyChain.Count() == 0)
            return GetNestedProperty(propertyChain, type);

        //Is Enumerable
        if (type.GetProperty(first).PropertyType.GetInterfaces().Any(t => t.Name == "IEnumerable"))
            return GetNestedProperty(
                propertyChain,
                type.GetProperty(first).PropertyType.GetGenericArguments()[0]);

        return GetNestedProperty(
            propertyChain,
            type.GetProperty(first).PropertyType.GetProperty(propertyChain.FirstOrDefault()).GetType());

    }

I am having difficulty generating the expression in the OrderByName extension method. I have tried quite a few things now, but the problem is that Payroll number does not exist in the Company class.

Is what I am trying to achieve even possible?

Any help with this would be greatly appreciated.

Your dynamic approach is going to need to end up with all of what your static approach needs to have. Your static approach has a `First` call. That obviously needs to also be in your dynamic approach if you want it to function the same way. — Servy, Jan 09 '15 at 16:29
Thanks Servy, of course that makes sense. I only wrote the static linq to explain the question, I had been thinking I was trying to genrate .OrderBy(x => x.Pieces.PayrollNo) — Paul Pierce, Jan 09 '15 at 16:42
And given that that wouldn't work statically, obviously that won't work when generating it dynamically. That's why you should always have a static example of what you're trying to create dynamically, and you need to be sure that your dynamic solution matches it for that specific case. Clearly your's doesn't match. — Servy, Jan 09 '15 at 16:44
Thanks Servy, it seems obvious now. I will move the expression building into the recursive method and get it working today. — Paul Pierce, Jan 12 '15 at 09:28

score 1 · Answer 1 · answered Jan 16 '15 at 11:47

NHibernate has other query APIs which are better for this.

string property = "Employees.PayrollNo";

var query = session.QueryOver<Company>()
    .Fetch(x => x.Employees).Eager;

// Join on the associations involved
var parts = property.Split('.');
var criteria = query.UnderlyingCriteria;
for (int i = 0; i < parts.Length - 1; i++)
{
    criteria.CreateAlias(parts[i], parts[i]);
}
// add the order
criteria.AddOrder(new Order(property, !isDescending));

var companies = query.List();

Dynamic Linq to OrderBy Object Nested in IEnumerable

1 Answers1