Dynamic LINQ Expression for sorting navigation property

Question

MVC3, Entity Framework 4.1 Code first.

Working with 2 tables

Model:

public class UniversityMaster
{
   [Key]
   public string UniversityId { get; set; }
   public string UniversityName { get; set; }

}

public class ProgramMaster
{
    [Key]
    public string ProgramId { get; set; }
    public string ProgramName { get; set; }
    public string UniversityId { get; set; }
    public virtual UniversityMaster University { get; set; } // navigation property

}

Dynamic expression for sorting (just to avoid a switch case statement):

public virtual IQueryable< ProgramMaster > GetQueryableSort(string sortField="", string sortDirection="")
   {
      IQueryable<ProgramMaster> query = _dbSet;
      ParameterExpression pe = Expression.Parameter(typeof(ProgramMaster), string.Empty);
      MemberExpression property = Expression.PropertyOrField(pe, sortField);
     //get a exception here if the sort field is of navigation property                            (University.UniversityName)
      LambdaExpression lambda = Expression.Lambda(property, pe);
      if (sortDirection == "ASC")
         orderbydir = "OrderBy";
      else
         orderbydir = "OrderByDescending";
         MethodCallExpression call = Expression.Call(typeof(Queryable),
         orderbydir, new Type[] { typeof(TEntity), property.Type }, query.Expression,  Expression.Quote(lambda));

      var returnquery = (IOrderedQueryable<ProgramMaster>)query.Provider.CreateQuery< ProgramMaster >(call);
      return returnquery;
    }

The page is displaying a grid with two columns Program Name and University Name using webgrid. The sorting work fine for Program Name column, however fails if sorted by University Name as this property is in UniversityMaster and the Expression.PropertyOrField searches this property in ProgramMaster. Here is the exception:

University.UniversityName' is not a member of type 'App.Core.Model.ProgramMaster

My question is how I make this work for navigation properties of my model class.

Hope I was able explain the scenario. Any help is appreciated.

Answer 1

Well that's because the MemberExpression is trying to call a member named Univerty.UniversityName on the parameter. What you want to do is call a member named Univerity on the parameter, then call UniversityName on that. Effectively, you need to iteratively resolve the property names.

public virtual IQueryable< ProgramMaster > GetQueryableSort(string sortField = "", string sortDirection = "")
{
    IQueryable<ProgramMaster> query = _dbSet;

    var propertyNames = sortField.Split(".");

    ParameterExpression pe = Expression.Parameter(typeof(ProgramMaster), string.Empty);
    Expression property = pe;
    foreach(var prop in propertyName)
    {
        property = Expression.PropertyOrField(property, prop);
    }

    LambdaExpression lambda = Expression.Lambda(property, pe);

    if (sortDirection == "ASC")
        orderbydir = "OrderBy";
    else
        orderbydir = "OrderByDescending";

    MethodCallExpression call = Expression.Call(
        typeof(Queryable),
        orderbydir, 
        new Type[] { typeof(TEntity), property.Type }, 
        query.Expression, 
        Expression.Quote(lambda));

    var returnquery = (IOrderedQueryable<ProgramMaster>)query.Provider.CreateQuery<ProgramMaster>(call);

    return returnquery;
}

Answer 2

Microsoft has a DynamicQueryable class which can be used to dynamically construct certain portions of a LINQ query using strings. With this you can say myQuery.OrderBy("University.UniversityName") and it will handle building the expression. The same library also supports dynamic construction of SELECT and WHERE clauses.

You can find a copy of the source as part of the excellent EntityFramework.Extended package by Loresoft. Microsoft's file is at https://github.com/loresoft/EntityFramework.Extended/blob/master/Source/EntityFramework.Extended/Dynamic/DynamicQueryable.cs