Implementing filters for a SQL table display on a view in ASP.NET MVC (C#) and LINQ-to-SQL?

Question

A follow-up from this question, I've changed my controller and routing around so that now the sort value is assigned by ?sort= however I also want to implement the ability for users to filter the table based on a select set of values:

Technician : Tech1, Tech2, Tech3, etc. Category : Category1, Category2, Category3, etc. Priority : Priority1, Priority2, Priority3, etc.

I am only using one table at the moment, so the easiest way to get the list of available categories to filter on is to select distinct values of category from the table. They are all input via HTML s so I have control over what gets put in. I don't want to split them all into different tables just yet, I'm trying to do as much as I can with one table for now but if this is the scenario that breaks the one-table-model then fine, I can adjust.

To clarify the use case it is probably best to show a quick screenshot of the view in question:

Click to view full-size.

I want to implement a system whereby a user could for example filter "Priority" to only show issues where priority was "Investigative.

I'm looking for suggestions on how to implement this on both the front and back end. I've tried to find a site that does something similar with tables of data but I can't think of one of the top of my head although I'm sure this is a feature that has been implemented a hundred times before!

Can anyone recommend a good way to do this?

Answer 1

I don't fully understand the use-case - but you can add filters easily via IQueryable<T>:

var query = /* your primary query */

if(!string.IsNullOrEmpty(name)) {
    query = query.Where(row => row.Name == name);
}

if(activeOnly) {
    query = query.Where(row => row.IsActive);
}

etc.

You can do more sophisticated things by building an Expression manually, but the above has the advantage of being easy to debug, and compiler-checked (the compiler will ensure there is a suitable IsActive property, etc)

Answer 2

Server tier

Let's start with some pseudocode.

public ActionResult Open(string sort, string technician, 
    string category, string priority)
{
    // generate query
    // filter stuff
    // order/sort stuff
}

It's important to get the order of operations right, here. Certain IQueryable providers (I'm looking at you, Entity Framework) will silently ignore ordering if it comes before filtering. So start by generating an unordered, unfiltered query, as with your last question, then append the filtering, and finally append the ordering.

Note that I have added an argument to your action for every possible filter case. That's one way to do it. See other way to do it would be to allow filtering on just about anything, by iterating the query string parameters (inside of Request) in treating anything not called "sort" as a potential filter. You should choose what fits your application best.

Now, how do you actually implement the filtering? What Marc showed is one way. That works really well if there are only certain cases you intend to filter. Obviously, I would put it in a helper method, rather than in the action itself. Chances are good you need to reuse this helper and other actions. However, if you intend to offer filtering on more rows than you care to spell out inside a single method, another way would be to use the Microsoft Dynamic LINQ library, which you can get from CodePlex. This allows you to build a LINQ .Where using strings instead of lambda expressions.

Browser tier

Now that your application can handle this, you need a method of creating a link that looks like:

http://example.com/Issue/Open?sort=ID&priority=Investigative

If you have a menu of filter options, for example, each menu item would correspond to a link with different query string parameters.

This is a little tricky, because you probably want to preserve the existing query string parameters when adding a new one. For example, if a user has sorted the open issues by ID, and chooses to filter only investigative priority issues, you'd still like to preserve the sort. So all of the links in the menu need to include the query string parameters for the currently displayed page as well as the query string parameter for the menu item itself.

So the general approach that we take when doing this is, for each item in the "filtering menu" (or whatever link your building):

1. Create a RouteValueDictionary of query string parameters
2. Add all query string parameters passed to the current view to the dictionary.
3. Add an additional query string parameter for the current menu item.
4. Generate the final URL using Html.RouteLink and the dictionary we built.