How to save time when pulling data from DB using Entity Framework Core in controller with paging?

Question

I am in a situation my web based application having slow performance after reading data from DB (100-200 records) before displaying on the page with paging enabled. I followed the example from Microsoft: https://learn.microsoft.com/en-us/aspnet/core/data/ef-rp/sort-filter-page?view=aspnetcore-6.0#add-paging

I found that when my code loops through the data from the database, processes it and adds it into a list, it takes about half second for each record, more or less, according to the execution time I output.

The example from Microsoft does:

1. Pull the data
2. Process the data depends on input for sorting, filtering
3. Page the data before return to the view to display

That sounds like it will cause performance issue when there is a large number of records in the DB as it pull everything first, then page it.

I am wondering what is the right approach to improve the performance? Also should I save the data in cache/session to save time, when the page is refreshed or previous/next page button is clicked?

Thank you.

UPDATE

Below is the key part of the Index Action in the Controller:

public IActionResult Index(string sortOrder, string currentFilter, string searchString, int? page)
{
    // Read all raw data from DB
    var allRequests = (from r in _context.Request).ToList();

    // Create the ViewModel object List
    List<CreateRequestViewModel> createRequestList = new List<CreateRequestViewModel>();

    foreach (Request request in allRequests)
    {
        CreateRequestViewModel createRequest = new CreateRequestViewModel();
        createRequest.RequestId = request.RequestId;
        // Some customized code to assign value to the ViewModel object's properties

        // Add the ViewModel object to the List
        createRequestList.Add(createRequest);
    }

    // Return the PaginatedList using ViewModel List created above with paging to the page.
    return View(PaginatedList<CreateRequestViewModel>.CreateAsync(createRequestList.AsQueryable(), page ?? 1, pageSize));
}

public static PaginatedList<T> CreateAsync(IQueryable<T> source, int pageIndex, int pageSize)
{
    var count = source.Count();
    var items = source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
    return new PaginatedList<T>(items, count, pageIndex, pageSize);
}

Answer 1

Usually if you want pagination on the server side, you have to work with IQueryable created from DbSet<>

public IActionResult Index(string sortOrder, string currentFilter, string searchString, int? page)
{
    var query = _context.Request.AsQueryable();

    // apply filters to query
    ...

    var result = query.Select(r => new CreateRequestViewModel
    {
        RequestId = r.RequestId,
        // ... other fields
    });

    
    // Return the PaginatedList using ViewModel List created above with paging to the page.
    return View(PaginatedList<CreateRequestViewModel>.CreateAsync(result, page ?? 1, pageSize));
}