Caching a list in ASP.NET MVC 5

Question

I have a thousands of users in my MVC APP and I use sort, paging and searching in my table of users (by this tutorial), but it takes a too long to load, sort or search.

So I have list of users

List<ApplicationUser> appUsers = _context.Users.ToList();

and I have list of models

List<RegisterViewModel> models = new List<RegisterViewModel>();
foreach (var au in appUsers)
{
    RegisterViewModel rvm = new RegisterViewModel(au);
    rvm.RoleName = UserManager.GetRoles(au.Id).First();
    models.Add(rvm); //new RegisterViewModel(au));
}

And in models I search, or sort.

What is the simplest way to cache this list of models and how can I use cached list instead of creating new list?

Here is my complete code of ActionResult Index()

[OutputCache(Duration = 120, VaryByParam = "*")]
[AuthLog(Roles = "SuperAdmin")]
public ActionResult Index(string sortOrder, string currentFilter, string searchString, int? page)
{
    ViewBag.CurrentSort = sortOrder;
    ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "Name_desc" : "";
    ViewBag.EmailSortParm = sortOrder == "Email" ? "Email_desc" : "Email";

    if (searchString != null)
        page = 1;
    else
        searchString = currentFilter;

    ViewBag.CurrentFilter = searchString;

    List<ApplicationUser> appUsers = _context.Users.ToList();

    List<RegisterViewModel> models = new List<RegisterViewModel>();
    foreach (var au in appUsers)
    {
        RegisterViewModel rvm = new RegisterViewModel(au);
        rvm.RoleName = UserManager.GetRoles(au.Id).First();
        models.Add(rvm); //new RegisterViewModel(au));
    }

    if (!String.IsNullOrEmpty(searchString))
        models = models.Where(s => s.Name.ToUpper().Contains(searchString.ToUpper()) || s.Email.ToUpper().Contains(searchString.ToUpper())).ToList();

    switch (sortOrder)
    {
        case "Name_desc":
            models = models.OrderByDescending(x => x.Name).ToList();
            break;
        case "Email_desc":
            models = models.OrderByDescending(x => x.Email).ToList();
            break;
        case "Email":
            models = models.OrderBy(x => x.Email).ToList();
            break;
        default:
            models = models.OrderBy(x => x.Name).ToList();
            break;
    }
    int pageSize = 20;
    int pageNumber = (page ?? 1);

    ViewBag.Title = "Použivatelia";
    return View(models.AsEnumerable().ToPagedList(pageNumber, pageSize));
}

Have you tried the [MemoryCache](https://msdn.microsoft.com/en-us/library/system.runtime.caching.memorycache(v=vs.110).aspx) class ? — Panagiotis Kanavos, Mar 16 '16 at 12:14
I would first investigate why it takes a long time to search and sort. Assuming you are using a database as the store you might be able to fix this with an index or 2, an indexed view, or something else. If the total user list is incredibly long OR change frequently an in memory store is probably not the best way to go. Even with millions of records in a data table, a fine grained query and proper indexes should return results relatively fast. Also, maybe its not even the database that's slow but something else you are loading. You will not be able to tell without some profiling. — Igor, Mar 16 '16 at 12:19

score 4 · Answer 1 · answered Mar 16 '16 at 12:22

4

I think you need to do your sorting and filtering in your database rather than memory.

Not sure what your -_context is but assume it's an ORM of some sort. Problem is you calling ToList before any sorting or filtering which means everything is done in memory by your code. If you use your ORM capabilities you can probably avoid the need for caching by simply allow it to generate more effective SQL queries.

answered Mar 16 '16 at 12:22

Daniel van Heerden

836
1
7
25

1

I agree. Note that [the tutorial that the OP referenced](http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/sorting-filtering-and-paging-with-the-entity-framework-in-an-asp-net-mvc-application) does not call `ToList` inline as in the OP's code. Keeping a whole table in memory is probably not going to be faster than filtering the data *before* it is fetched. – NightOwl888 Mar 16 '16 at 12:25
I dont have a problem only with sort or search in my list, but also with my initial load. Initial load takes about 70 seconds --> it is really bad. – Kicker Mar 16 '16 at 12:53
All the more reason to make sure you filter the data you initially load instead of loading it all in one go – Daniel van Heerden Mar 16 '16 at 13:08

score 0 · Answer 2 · answered Dec 19 '17 at 21:30

You need to be doing paging and sorting against the database and not pulling back thousands of rows and then doing paging or sorting against that rows.

When you call .ToList() it is signally to the database you want all the records back and then it pulls back the thousands in your table and only then are you applying your sorting and paging to these that have been brought back into memory.

Instead of the query only pulling back 10 sorted records based on the page size and page number you are pulling back say 4000 and then doing a sort/paging.

See article about PagedList and sorting

Caching a list in ASP.NET MVC 5

2 Answers2