如何用PagedList实现真实分页？解决1万条数据分页卡顿问题

Hey there! The issue you're facing is super common—when you call ToList() before applying pagination, you're pulling all 10k records into memory first, which kills performance. The fix is to leverage deferred execution with IQueryable so pagination happens at the database level, not in your app. Let's walk through each component step by step with working examples:

1. Model (Usuario) #

First, your core entity class representing the database table:

public class Usuario
{
    public int Id { get; set; }
    public string Nome { get; set; }
    public string Email { get; set; }
    // Add any other properties matching your table columns
}

2. Search Model (UsuarioSearchModel) #

This model handles pagination parameters and optional search filters:

public class UsuarioSearchModel
{
    public int PageNumber { get; set; } = 1; // Default to first page
    public int PageSize { get; set; } = 20; // Default to 20 records per page
    // Optional: Add search criteria for filtering results
    public string SearchNome { get; set; }
}

3. Generic DAO #

The key here is to return IQueryable<T> instead of List<T>—this keeps the query deferred until pagination is applied, so the database only returns the needed records:

public class GenericDao<T> where T : class
{
    private readonly DbContext _context;
    private readonly DbSet<T> _dbSet;

    public GenericDao(DbContext context)
    {
        _context = context;
        _dbSet = context.Set<T>();
    }

    // Critical: Returns IQueryable to enable deferred execution
    public IQueryable<T> GetAll()
    {
        return _dbSet.AsNoTracking(); // AsNoTracking boosts performance for read-only operations
    }

    // Optional: Add filtered query support
    public IQueryable<T> GetFiltered(Expression<Func<T, bool>> filter)
    {
        return _dbSet.Where(filter).AsNoTracking();
    }
}

4. View Model (ViewUsuario) #

This wraps your paged data and search parameters to pass to the view:

using PagedList;

public class ViewUsuario
{
    public IPagedList<Usuario> Usuarios { get; set; }
    public UsuarioSearchModel SearchModel { get; set; }
}

5. Controller (UsuarioController) #

Here's where you tie everything together—apply filters, execute pagination at the database level, and pass data to the view:

using PagedList;

public class UsuarioController : Controller
{
    private readonly GenericDao<Usuario> _usuarioDao;

    // Inject your GenericDao via dependency injection
    public UsuarioController(GenericDao<Usuario> usuarioDao)
    {
        _usuarioDao = usuarioDao;
    }

    public ActionResult Index(UsuarioSearchModel searchModel)
    {
        // 1. Get the base IQueryable (NO ToList() here!)
        var query = _usuarioDao.GetAll();

        // 2. Apply optional filters (runs at database level)
        if (!string.IsNullOrEmpty(searchModel.SearchNome))
        {
            query = query.Where(u => u.Nome.Contains(searchModel.SearchNome));
        }

        // 3. Apply pagination—ToPagedList generates database-level pagination SQL
        var pagedUsuarios = query.ToPagedList(searchModel.PageNumber, searchModel.PageSize);

        // 4. Prepare view model
        var viewModel = new ViewUsuario
        {
            Usuarios = pagedUsuarios,
            SearchModel = searchModel
        };

        return View(viewModel);
    }
}

6. View (Index.cshtml) #

Display the paged data and add pagination navigation:

@model ViewUsuario
@using PagedList.Mvc;
@using PagedList;

@{
    ViewBag.Title = "Lista de Usuários";
}

<h2>Usuários</h2>

<!-- Optional Search Form -->
@using (Html.BeginForm("Index", "Usuario", FormMethod.Get))
{
    <div class="mb-3">
        @Html.LabelFor(m => m.SearchModel.SearchNome, "Buscar por Nome:")
        @Html.TextBoxFor(m => m.SearchModel.SearchNome, new { @class = "form-control" })
    </div>
    <button type="submit" class="btn btn-primary">Buscar</button>
}

<!-- Paged Data Table -->
<table class="table table-striped mt-4">
    <thead>
        <tr>
            <th>ID</th>
            <th>Nome</th>
            <th>Email</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var usuario in Model.Usuarios)
        {
            <tr>
                <td>@usuario.Id</td>
                <td>@usuario.Nome</td>
                <td>@usuario.Email</td>
            </tr>
        }
    </tbody>
</table>

<!-- Pagination Navigation -->
<div class="pagination">
    @Html.PagedListPager(Model.Usuarios, page => Url.Action("Index", new { 
        pageNumber = page, 
        pageSize = Model.SearchModel.PageSize,
        searchNome = Model.SearchModel.SearchNome
    }), PagedListRenderOptions.Classic)
</div>

<!-- Pagination Info -->
<p class="mt-2">
    Mostrando @Model.Usuarios.FirstItemOnPage a @Model.Usuarios.LastItemOnPage de @Model.Usuarios.TotalItemCount registros
</p>

Key Notes to Avoid Performance Issues: #

• Never call ToList() before ToPagedList(): This is the main mistake that pulls all records into memory. IQueryable keeps the query deferred until you execute it with ToPagedList().
• Install Required NuGet Packages: You'll need PagedList and PagedList.Mvc (use the NuGet Package Manager to install these).
• Use AsNoTracking(): This disables Entity Framework's change tracking for read-only operations, which speeds up queries.

内容的提问来源于stack exchange，提问作者FernandoPaiva