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

    I am not sure about what your actual concern is. The code is readable and at least a first pass looks like it should work as expected. It is easy to follow. Sometimes the logic requires this type of complexity but to me its looks pretty clean. I could write the query as a single line but runtime effectively will be the same but it would be much harder to understand.

    I was hoping there was some cleaner/shorter way to do the filtering (the three if-statements in this case) and the sorting (the switch-statement). In this example, I'm only filtering and sorting on 3 fields, but I also have list where I needs 6 fields or more. That quickly leads to a lot of code and it looks ugly. How would you write it on a single line and where does my logic suck? :-)

    I could write the entire line as a single line of code. That would not make it any better just shorter. Code golf is fine for honing skills but making readable, and maintainable code is far more valuable.

    And I edited my first comment. Your logic doesnt suck. Sometimes the logic gets complex which sucks. As I said I would be quite happy if i was handed this to maintain. By contrast as a single command line would include many compares that replace the if statements. The end result query that gets executed would probably be the same or maybe even worse. But it would be much harder to follow.

    Oh, OK, I understand. I agree that the code is easy to read and understand, but it's kind of a drag to _write_ it :-) I was hoping for some improvement in that department without sacrificing the readability.

  • shuniar

    shuniar Correct answer

    10 years ago

    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.


License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM