How to create fastest searching filtering data in MVC

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.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

arvind baldaniya9y ago
@Murugesan Thank u
0
Manikandan Murugesan9y ago
For additional information refer the following Links :
http://www.itworld.com/article/2956575/development/how-to-sort-search-and-paginate-tables-in-asp-net-mvc-5.html

https://www.mikesdotnetting.com/article/243/mvc-5-with-ef-6-in-visual-basic-sorting-filtering-and-paging

http://www.devcurry.com/2012/08/filtering-data-in-aspnet-mvc-using.html

http://www.c-sharpcorner.com/UploadFile/abhikumarvatsa/filter-records-in-mvc/

https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/sorting-filtering-and-paging-with-the-entity-framework-in-an-asp-net-mvc-application

https://msdn.microsoft.com/en-us/library/gg416513(VS.98).aspx
0
Manikandan Murugesan9y 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.
0