Dynamic filtering and sorting with Entity Framework

Question

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<UserListItem>
        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 => u.FirstName.Contains(search.Name) || u.LastName.Contains(search.Name));
        if (!String.IsNullOrWhiteSpace(search.Email)) users = users.Where(u => u.Email.Contains(search.Email));
        if (search.UsertypeId.HasValue) users = users.Where(u => 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 => u.FirstName).ThenBy(u => u.LastName);
                break;
            case "namedesc":
                users = users.OrderByDescending(u => u.FirstName).ThenByDescending(u => u.LastName);
                break;
            case "emailasc":
                users = users.OrderBy(u => u.Email);
                break;
            case "emaildesc":
                users = users.OrderByDescending(u => u.Email);
                break;
            case "typeasc":
                users = users.OrderBy(u => u.UsertypeSortingOrder);
                break;
            case "typedesc":
                users = users.OrderByDescending(u => 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);
    }
}

Answer 1

I know this is old, but thought it may be helpful for anyone reading this. If you want to clean up the code, you can always refactor it.. something like this is more readable than the original:

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

    public ActionResult Index(int page = 1, string sort = "", UserSearchViewModel search)
    {
        var users = GetUsers(search, sort);
        var totalPages = GetTotalPages(users);

        var viewModel = new UsersIndexViewModel
        {
            Users = users.Skip(PageSize * (page - 1)).Take(PageSize).ToList(),
            TotalPages = totalPages
        };

        return View(viewModel);
    }

    private UserListItem GetUsers(UserSearchViewModel search, string sort)
    {
        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
                    };

        users = FilterUsers(users, search);
        users = SortUsers(users, sort);

        return users;
    }

    private UserListItem SortUsers(object users, string sort)
    {
        switch (sort.ToLower())
        {
            default:
                users = users.OrderBy(u => u.FirstName).ThenBy(u => u.LastName);
                break;
            case "namedesc":
                users = users.OrderByDescending(u => u.FirstName).ThenByDescending(u => u.LastName);
                break;
            case "emailasc":
                users = users.OrderBy(u => u.Email);
                break;
            case "emaildesc":
                users = users.OrderByDescending(u => u.Email);
                break;
            case "typeasc":
                users = users.OrderBy(u => u.UsertypeSortingOrder);
                break;
            case "typedesc":
                users = users.OrderByDescending(u => u.UsertypeSortingOrder);
                break;
        }
        return users;
    }

    private UserListItem FilterUsers(object users, UserSearchViewModel search)
    {
        if (!String.IsNullOrWhiteSpace(search.Name)) users = users.Where(u => u.FirstName.Contains(search.Name)
                                                                              || u.LastName.Contains(search.Name));
        if (!String.IsNullOrWhiteSpace(search.Email)) users = users.Where(u => u.Email.Contains(search.Email));
        if (search.UsertypeId.HasValue) users = users.Where(u => u.UsertypeId == search.UsertypeId.Value);
        return users;
    }

    private int GetTotalPages(UserListItem users)
    {
        var filteredCount = users.Count();
        return Convert.ToInt32(Math.Ceiling((decimal)filteredCount / (decimal)PageSize));
    }
}

You can then refactor this further by moving these methods into a service class if you want to.

Answer 2

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:

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

The first part is a delegate (of type Predicate<TSearch>) 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<TSearch>

^{(it could also have been written as Func<TSearch, bool>)}

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<TItem> QueryMutator<TItem, TSearch>(IQueryable<TItem> 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<TItem, TSearch>
{
    public Predicate<TSearch> Condition { get; set; }
    public QueryMutator<TItem, TSearch> Mutator { get; set; }

    public SearchFieldMutator(Predicate<TSearch> condition, QueryMutator<TItem, TSearch> mutator)
    {
        Condition = condition;
        Mutator = mutator;
    }

    public IQueryable<TItem> Apply(TSearch search, IQueryable<TItem> 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<SearchFieldMutator<UserListItem, UserSearchViewModel>> SearchFieldMutators { get; set; }

We'll then populate the list:

SearchFieldMutators = new List<SearchFieldMutator<UserListItem, UserSearchViewModel>>
{
    new SearchFieldMutator<UserListItem, UserSearchViewModel>(search => !string.IsNullOrWhiteSpace(search.Name), (users, search) => users.Where(u => u.FirstName.Contains(search.Name) || u.LastName.Contains(search.Name))),
    new SearchFieldMutator<UserListItem, UserSearchViewModel>(search => !string.IsNullOrWhiteSpace(search.Email), (users, search) => users.Where(u => u.Email.Contains(search.Email))),
    new SearchFieldMutator<UserListItem, UserSearchViewModel>(search => search.UsertypeId.HasValue, (users, search) => users.Where(u => u.UsertypeId == search.UsertypeId.Value)),
    new SearchFieldMutator<UserListItem, UserSearchViewModel>(search => search.CurrentSort.ToLower() == "namedesc", (users, search) => users.OrderByDescending(u => u.FirstName).ThenByDescending(u => u.LastName)),
    new SearchFieldMutator<UserListItem, UserSearchViewModel>(search => search.CurrentSort.ToLower() == "emailasc", (users, search) => users.OrderBy(u => 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 = "[email protected]", UsertypeId = null },
    new UserListItem { FirstName = "Tugboat", LastName = "Captain", Email = "[email protected]", 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

Answer 3

Sorting functionality may be implemented in more declarative syntax. First declare associative dictionary as private member of the class.

    private Dictionary<string, Func<IQueryable<UserListItem>, IQueryable<UserListItem>>> _sortAssoc = new Dictionary<string, Func<IQueryable<UserListItem>, IQueryable<UserListItem>>>(StringComparer.OrdinalIgnoreCase)
    {
        { default(string),      users => users.OrderBy(u => u.FirstName).ThenBy(u => u.LastName)},
        { "namedesc",           users => users.OrderByDescending(u => u.FirstName).ThenByDescending(u => u.LastName)} ,
        { "emailasc",           users => users.OrderBy(u => u.Email) },
        { "emaildesc",          users => users.OrderByDescending(u => u.Email) },
        //...           
    };

then you can call suitable sorting method this way:

users = _sortAssoc.ContainsKey(sort) ? _sortAssoc[sort](users) : _sortAssoc[default(string)](users);

Answer 4

If you used the ADO.NET Entity Framework Generator for EF 4.1, you can write your code like below.

the way is to construct a sort string. " order by personname asc" will be written like below "it.personname asc" - the "it" is used internally by EF.

string sortfield = "personname";
string sortdir= "asc";

IQueryable<vw_MyView> liststore= context.vw_MyView
                            .OrderBy("it." + sortfield  + " " + sortdir)
                            .Where(c => c.IsActive == true
                                && c.FundGroupId == fundGroupId
                                && c.Type == 1
                                );

I've used this only for the ADO.NET EF generator. DBcontext for EF 4.3.1 do not support this feature.

Answer 5

I'd consider using EntitySQL as a replace to LINQ to Entities. With EntitySQL you would concatenate the sort field name with a statement. Although there is a big disadvantage of no compile time check.

Answer 6

Another option:

• Create a domain object that takes the parameters needed for the filter
• Create a function on the repository that takes that filter domain object and within that repository function handle the filtering/sorting/paging
• Call that function from your upper layers (whether it is the business layer/service or MVC controller) passing in the filter domain object

Advantages:

1. If you ever want to enlarge the filter you need only add to the 'filter' domain object
2. AND modify the repository to handle that new filter
3. Everywhere else you want to get that set of data, the caller only needs to populate the filter as needed in that situation

Anyhow for what it's worth, that has proved to be the best way for handling this situation with scalability in mind as well as reducing chance of duplication of code.

Answer 7

I've written a TON of code like this as well...

I've read about "dynamic LINQ" but that's not going to address the issue when you need to orderby().thenby(). The only things I've done differently is using enums for field names and sort directions which works great in my WebForms apps using ViewState but I'm not sure how I'd maintain the "sort state" in MVC ;)

If your table gets large you may consider server-side paging in stored procedures rather than bringing back the whole database then sorting it locally, but that's another topic :)