Dynamic filtering and sorting with Entity Framework I'm developing an application using ASP.NET MVC 3 and Entity Framework 4.1. In that application I have a lot of paged lists. Users can filter and sort these lists.

This results in code like the one below. I'm not really happy with this code. Is there a better way to do the filtering and sorting with Entity Framework?

Some of you might suggest putting that code into a service class and not in the controller, but that would just move the ugly code somewhere else. Instead of ugly code in a controller, I'd end up with ugly code in a service.

public UsersController : Controller
{
    private const int PageSize = 25;

    public ActionResult Index(int page = 1, string sort = "", UserSearchViewModel search)
    {
        // Get an IQueryable&lt;UserListItem&gt;
        var users = from user in context.Users
                    select new UserListItem
                    {
                        UserId = user.UserId,
                        Email = user.Email,
                        FirstName = user.FirstName,
                        LastName = user.LastName,
                        UsertypeId = user.UsertypeId,
                        UsertypeDescription = users.Usertype.Description,
                        UsertypeSortingOrder = users.Usertype.SortingOrder
                    };

        // Filter on fields when needed
        if (!String.IsNullOrWhiteSpace(search.Name)) users = users.Where(u =&gt; u.FirstName.Contains(search.Name) || u.LastName.Contains(search.Name));
        if (!String.IsNullOrWhiteSpace(search.Email)) users = users.Where(u =&gt; u.Email.Contains(search.Email));
        if (search.UsertypeId.HasValue) users = users.Where(u =&gt; u.UsertypeId == search.UsertypeId.Value);

        // Calculate the number of pages based on the filtering
        int filteredCount = users.Count();
        int totalPages = Convert.ToInt32(Math.Ceiling((decimal)filteredCount / (decimal)PageSize));

        // Sort the items
        switch(sort.ToLower())
        {
            default:
                users = users.OrderBy(u =&gt; u.FirstName).ThenBy(u =&gt; u.LastName);
                break;
            case "namedesc":
                users = users.OrderByDescending(u =&gt; u.FirstName).ThenByDescending(u =&gt; u.LastName);
                break;
            case "emailasc":
                users = users.OrderBy(u =&gt; u.Email);
                break;
            case "emaildesc":
                users = users.OrderByDescending(u =&gt; u.Email);
                break;
            case "typeasc":
                users = users.OrderBy(u =&gt; u.UsertypeSortingOrder);
                break;
            case "typedesc":
                users = users.OrderByDescending(u =&gt; u.UsertypeSortingOrder);
                break;
        }

        // Apply the paging
        users = users.Skip(PageSize * (page - 1)).Take(PageSize);

        var viewModel = new UsersIndexViewModel
                        {
                            Users = users.ToList(),
                            TotalPages = totalPages
                        };

        return View(viewModel);
    }
}
EDIT: I apologize from my earlier sample that didn't quite compile. I've fixed it and added a more complete example.


You could associate each condition with a strategy for changing the query. Each strategy (called SearchFieldMutator in this example) will hold two things:


A way to decide if to apply the strategy.
The strategy itself.


The first part is a delegate (of type Predicate&lt;TSearch&gt;) that returns true or false based on the data in the UserSearchViewModel (or any other type, since it only defines a generic type TSearch). If it return true, the strategy is applied. If it returns false, it's not applied. This is the type of the delegate:

Predicate&lt;TSearch&gt;


(it could also have been written as Func&lt;TSearch, bool&gt;)

The second part is the strategy itself. It is supposed to 'mutate' the query by applying a LINQ operator to it, but really just returns a new query with the added operator, and the caller of it should discard the old query and keep the new one. So it's not really mutation, but it has the same effect. I've created a new delegate type for it, so its usage is clear:

public delegate IQueryable&lt;TItem&gt; QueryMutator&lt;TItem, TSearch&gt;(IQueryable&lt;TItem&gt; items, TSearch search);



  NOTE: I've defined both the item type and the search data as generic types (as TItem and TSearch, respectively) so this code is usable in multiple locations in your code. But if this is confusing, you can remove the generics completely, and replace any TItem with UserListItem and any TSearch with UserSearchViewModel.


Now that we have defined the two types of the strategy, we can create a class that holds them both, and also does the (simulated) mutation:

public class SearchFieldMutator&lt;TItem, TSearch&gt;
{
    public Predicate&lt;TSearch&gt; Condition { get; set; }
    public QueryMutator&lt;TItem, TSearch&gt; Mutator { get; set; }

    public SearchFieldMutator(Predicate&lt;TSearch&gt; condition, QueryMutator&lt;TItem, TSearch&gt; mutator)
    {
        Condition = condition;
        Mutator = mutator;
    }

    public IQueryable&lt;TItem&gt; Apply(TSearch search, IQueryable&lt;TItem&gt; query)
    {
        return Condition(search) ? Mutator(query, search) : query;
    }
}


This class holds both the condition and the strategy itself, and by using the Apply() method, we can easily apply it to a query if the condition is met.

We can now go create out list of strategies. We'll define some place to hold them (on one of your classes), since they only have to be created once (they are stateless after all):

List&lt;SearchFieldMutator&lt;UserListItem, UserSearchViewModel&gt;&gt; SearchFieldMutators { get; set; }


We'll then populate the list:

SearchFieldMutators = new List&lt;SearchFieldMutator&lt;UserListItem, UserSearchViewModel&gt;&gt;
{
    new SearchFieldMutator&lt;UserListItem, UserSearchViewModel&gt;(search =&gt; !string.IsNullOrWhiteSpace(search.Name), (users, search) =&gt; users.Where(u =&gt; u.FirstName.Contains(search.Name) || u.LastName.Contains(search.Name))),
    new SearchFieldMutator&lt;UserListItem, UserSearchViewModel&gt;(search =&gt; !string.IsNullOrWhiteSpace(search.Email), (users, search) =&gt; users.Where(u =&gt; u.Email.Contains(search.Email))),
    new SearchFieldMutator&lt;UserListItem, UserSearchViewModel&gt;(search =&gt; search.UsertypeId.HasValue, (users, search) =&gt; users.Where(u =&gt; u.UsertypeId == search.UsertypeId.Value)),
    new SearchFieldMutator&lt;UserListItem, UserSearchViewModel&gt;(search =&gt; search.CurrentSort.ToLower() == "namedesc", (users, search) =&gt; users.OrderByDescending(u =&gt; u.FirstName).ThenByDescending(u =&gt; u.LastName)),
    new SearchFieldMutator&lt;UserListItem, UserSearchViewModel&gt;(search =&gt; search.CurrentSort.ToLower() == "emailasc", (users, search) =&gt; users.OrderBy(u =&gt; u.Email)),
    // etc...
};


We can then try to run it on a query. Instead of an actual Entity Framework query, I'm going to use a simply array of UserListItems and add a .ToQueryable() on to it. It will work the same if you replace it with an actual database query. I'm also going to create a simple search, for the sake of the example:

// This is a mock EF query.
var usersQuery = new[]
{
    new UserListItem { FirstName = "Allon", LastName = "Guralnek", Email = null, UsertypeId = 7 },
    new UserListItem { FirstName = "Kristof", LastName = "Claes", Email = "whoknows@example.com", UsertypeId = null },
    new UserListItem { FirstName = "Tugboat", LastName = "Captain", Email = "tugboat@ahoy.yarr", UsertypeId = 12 },
    new UserListItem { FirstName = "kiev", LastName = null, Email = null, UsertypeId = 7 },
}.AsQueryable();

var searchModel = new UserSearchViewModel { UsertypeId = 7, CurrentSort = "NameDESC" };


The following actually does all the work, it changes query inside the usersQuery variable to the one specified by all the search strategies:

foreach (var searchFieldMutator in SearchFieldMutators)
    usersQuery = searchFieldMutator.Apply(searchModel, usersQuery);


That's it! This is the result of the query:  



You can try running it yourself. Here's a LINQPad query for you to play around with:

http://share.linqpad.net/7bud7o.linq