Binding, Sorting, and Filtering

Objective

Binding a list of to a Gridview or similar is something we all do frequently and with ease, however, as soon as you want to throw in filtering and ordering things get more complicated. In these last couple of days at work I believe I have created a solution that helps deal with all the nuisance that comes along with using filtered data. Essentially what you want is to easily create your object, then bind the list of them to a grid, and then you're done. 

The UI

Skipping the paging, this is a simple grid with sortable columns and (!) a "contains" filter on various fields. The previous implementation of the page used ASP.classic and was building the SQL query as a string, making filtering and ordering somewhat simple by just appending SQL statements based on the sort and the filter. Obviously this tactic cannot work with LINQ querries (well, I suppose it can...)

Creating the class 

Theoretically, your "business" project's method will return you either the entities as an IQuerable or you are creating your "DataRow" object on the page from what the business project gives you. I will describe the latter approach. 

private class GridOrdersDataRow {
    [CORECommon.General.Display(Name = "OrderID")]
    public long OrderId { get; set; }
    [CORECommon.General.Display(Name = "Account Number")]
    public string AccountNumber { get; set; }
    [CORECommon.General.Display(Name = "EventID")]
    public long EventId { get; set; }
    public string FirstName { get; set; }
    public string SchoolName { get; set; }
    public string LastName { get; set; }
    [CORECommon.General.Display(Name = "Customer", DatabaseFields = new string[] { "FirstName", "LastName" })]
    public string FullName {
        get {
            return this.FirstName + " " + this.LastName;
        }
    }
}

For the filtering, you would add a custom attribute with the name you want to appear in the drop down list , and you have an option to specify DatabaseFields as seen with the "FullName" property. The DatabaseFields defaults to the property name or can be supplied as an array, this gives you full control over how your data is queried. As the name states the property name or supplied name must be the actual name of your IQueryable (not necessarily the actual database fieldnames, will consider the attribute property). If the attribute is not present the property is not enabled for filtering.

Binding, sorting, and filtering 

Binding the property filter drop-down done simply with :

ddlSearchProperty.DataSource = CORECommon.General.GetDisplayAttributes<GridOrdersDataRow>();
ddlSearchProperty.DataBind();

Ordering and filtering your data, OrderByWithDirection takes a string and a SortDirection which I had a Tuple at the time.

list = list.WhereContains(txtSearchText.Text, CORECommon.General.GetDatabaseFieldNames<GridOrdersDataRow>(ddlSearchProperty.SelectedValue));
list = list.OrderByWithDirection(sort.Item1, sort.Item2);

Enjoy results.

So basically, you've made a class and added properties for filtering. Other things that still have to be done manually which I would like to work out is creating the Header text for gridview from the class and the sort expression - these are in the markup of my page (pretty straightforward).

Under the hood.

All of this kind of started when M showed me this article on how to sort by using a string. From there I decided that instead of keeping random strings in random places I would use an attribute on my class, and from there I decided that the filtering need to work in a similar fashion and I came up with this :

public static IQueryable<T> WhereContains<T>(this IQueryable<T> source, object value, params string[] searchProperties) {
    var type = typeof(T);
    var lambdaBuilder = PredicateBuilder.False<T>();
    foreach (var searchProperty in searchProperties) {
        var property = type.GetProperty(searchProperty);
        var parameter = Expression.Parameter(type, property.Name);
        var propertyAccess = Expression.MakeMemberAccess(parameter, property);
        var convertedProperty = Expression.Call(Expression.Convert(propertyAccess, typeof(object)), typeof(object).GetMethod("ToString"));
        var contains = Expression.Call(convertedProperty,
                                        typeof(string).GetMethod("Contains", new[] { typeof(string) }),
                                        Expression.Constant(Convert.ToString(value)));

        var lambda = (Expression<bool>)Expression.Lambda(contains, parameter);
        lambdaBuilder = lambdaBuilder.Or(lambda);
                
    }
            
    return source.AsExpandable().Where(lambdaBuilder);
}

Really cool stuff.
Of course you can expand on this by simply adding more attributes and filtering types, whatever you require. For example, you could add a Where() not the contains version, which I actually started off with.