When creating dynamic sort and search queries in Linq in the Entity Framework

Question

I am trying to create a function for dynamically generating various queries based on some parameters. I am a little confused by LINQ syntax, and I'm not sure if I am doing this correctly.

A set of parameters of type String is "search" (for the value of the search text field), "searchfield" (what to look for), "limit_begin", "limit_end" for the number of lines and where to start. "order_by" for which the field is being ordered. "order_sort" for which to sort.

I discovered this "getpropertyvalue" reflection function in stackoverflow before, I hope this does what I intend, 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();
        }

"Orderby", VS2008 , , .

Answer 1

. - , , .

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

- , Convert.ToInt32, int.TryParse, .

1:

, PredicateBuilder LinqKit (http://www.albahari.com/nutshell/predicatebuilder.aspx).

2:

sting . , , . int, int, == . - :

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

Answer 2

.OrderBy .OrderByDescending Func<T, TKey>, . , , , . , , , - OrderBy.

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

, , . LINQ : http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx.

, !:)

Answer 3

.

Sort\Filter EF6 github,

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