Dexter Dexter - 1 month ago 19
ASP.NET (C#) Question

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

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

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