When creating dynamic linq sorting and searching order statements in Entity Framework

Question

I'm trying to build a function to dynamically generate different queries based on some parameters. I am a bit confused on LINQ syntax and I'm not sure if I'm doing it right.

The set of String type parameters are "search" (for search textbox value), "searchfield" (what to search), "limit_begin", "limit_end" for how many rows and where to start. "order_by" for which field to order by. "order_sort" for which way to sort.

I found this 'getpropertyvalue' reflection function on stackoverflow before, I'm hoping it does what I'm intending based on my own interpretation.

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

---

if (order_sort == "ASC")
        {
            (from a in entities.UserTable
             where GetPropertyValue(a, searchfield).ToString().Contains(search)
             select a)
                .OrderBy("a." + order_by)
                .Skip(Convert.ToInt32(limit_begin))
                .Take(Convert.ToInt32(limit_end))
                .ToList();
        }
        else if (order_sort == "DESC")
        {
            (from a in entities.UserTable
             where GetPropertyValue(a, searchfield).ToString().Contains(search)
             select a)
                .OrderByDescending("a." + order_by)
                .Skip(Convert.ToInt32(limit_begin))
                .Take(Convert.ToInt32(limit_end))
                .ToList();
        }

I'm getting an error on "Orderby" line, and VS2008 highlights it in red saying the type of argument cannot be inferred from the usage.

Answer 1

The .OrderBy and .OrderByDescending methods require a parameter of type Func<T, TKey> and you're passing a string. Basically, it's asking you for an expression it can use to identify the property you want to sort by. Since what you have to start with is a string, my best idea is to use reflection within your OrderBy.

.OrderBy(x => x.GetType().GetProperty(order_by).GetValue(x, null).ToString())

This isn't very easy to read, as you can see, but should do the trick. You could also look into the LINQ Dynamic Query Library at: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx.

Hope this helps! :)

Answer 2

This is how I've dealt with this in the past. Notice is is a single column search and sort, which it sounds like what you are going for.

var users = entities.UserTable;

// Setup the default order column.
Func<SweetEntity, string> orderFunc = u => u.Field1;

switch (searchfield)
{
    case "Field1":
        orderFunc = u => u.Field1;
        users = users.Where(u => u.Field1.Contains(search));
        break;
    case "Field2":
        orderFunc = u => u.Field2;
        users = users.Where(u => u.Field2.Contains(search));
        break;
}

// If you need to get the total count, do it here:
var totalUserCount = users.Count();

// Apply sorting:
if (order_sort == "ASC")
{
    users = users.OrderBy(orderFunc);
}
else
{
    users = users.OrderByDescending(orderFunc);
}

// Apply paging:
users = users.Skip(Convert.ToInt32(limit_begin)).Take(Convert.ToInt32(limit_end));

I would do something other than Convert.ToInt32, such as int.TryParse, but for the example I didn't.

Edit 1:

If you end up wanting a more robust search, look into PredicateBuilder in LinqKit (http://www.albahari.com/nutshell/predicatebuilder.aspx).

Edit 2:

My example just does sting contains in the filtering portion. Of course you could customize all that logic for the specific filter the user is requesting. If they're filtering on an int, you'd convert the filter string to an int, then can just compare with == in the lambda expression. Something like:

int myId;
if (int.TryParse(search, out myId))
{
    users = users.Where(u => u.SomeIntegerField == myId);
}

Answer 3

The subject of the original question is achievable easily with Expression Trees and reflection.

You can checkout a working example of adding strong type Sort \ Filter from just string column names and values to EF6 in a github repo I made for one of my talks

Here is a sample of sort functionality from the class in the link

 public class GridRequestSort
    {
        public string PropName { get; set; }
        public bool IsDescending { get; set; }
    }

        private static IQueryable<T> WrapSort<T>(
            IQueryable<T> query,
            GridRequestSort sort,
            bool isFirst = false)
        {
            var propAccessExpr = GetPropAccesssLambdaExpr(typeof(T), sort.PropName);
            var orderMethodName = "";
            if (isFirst)
            {
                orderMethodName = sort.IsDescending ? "OrderByDescending" : "OrderBy";
            } 
            else
            {
                orderMethodName = sort.IsDescending ? "ThenByDescending" : "ThenBy";
            }

            var method = typeof(Queryable).GetMethods().FirstOrDefault(m => m.Name == orderMethodName && m.GetParameters().Length == 2);
            var genericMethod = method.MakeGenericMethod(typeof(T), propAccessExpr.ReturnType);
            var newQuery = (IQueryable<T>)genericMethod.Invoke(null, new object[] { query, propAccessExpr });
            return newQuery;
        }

        private static LambdaExpression GetPropAccesssLambdaExpr(Type type, string name)
        {
            var prop = type.GetProperty(name, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
            var param = Expression.Parameter(type);
            var propAccess = Expression.Property(param, prop.Name);
            var expr = Expression.Lambda(propAccess, param);
            return expr;
        }