Pagination in .NET: With and Without Entity Framework

Pagination refers to the process of dividing a large dataset into smaller, manageable chunks, which can be fetched and displayed incrementally. This technique is essential for improving application performance and user experience, especially when dealing with large datasets.

Pagination with Entity Framework

Entity Framework (EF) simplifies database interactions in .NET applications by allowing developers to work with data using .NET objects. Here’s how you can implement pagination using EF.

Step 1. Setting Up the Project.

Assuming you have a .NET project set up with EF, let’s first create a simple model and DbContext.

Product.cs

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

ApplicationDbContext.cs

using Microsoft.EntityFrameworkCore;

public class ApplicationDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }
}

Step 2. Implementing Pagination Logic.

You can implement pagination using the Skip and Take methods provided by LINQ.

ProductService.cs

public class ProductService
{
    private readonly ApplicationDbContext _context;

    public ProductService(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<List<Product>> GetPaginatedProducts(int pageNumber, int pageSize)
    {
        return await _context.Products
            .Skip((pageNumber - 1) * pageSize)
            .Take(pageSize)
            .ToListAsync();
    }
}

ProductsController.cs

[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly ProductService _productService;

    public ProductsController(ProductService productService)
    {
        _productService = productService;
    }

    [HttpGet]
    public async Task<IActionResult> Get(int pageNumber = 1, int pageSize = 10)
    {
        var products = await _productService.GetPaginatedProducts(pageNumber, pageSize);
        return Ok(products);
    }
}

Step 3. Configuring Dependency Injection.

Program.cs

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllers();
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddScoped<ProductService>();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseDeveloperExceptionPage();
}

app.UseRouting();
app.UseEndpoints(endpoints =>
{
    endpoints.MapControllers();
});

app.Run();

Pagination Without Entity Framework

If you’re not using EF, you can still implement pagination using plain ADO.NET or any other data access technique.

Step 1. Setting Up the Data Access Layer.

Product.cs

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

ProductRepository.cs

using System.Data;
using System.Data.SqlClient;

public class ProductRepository
{
    private readonly string _connectionString;

    public ProductRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public List<Product> GetPaginatedProducts(int pageNumber, int pageSize)
    {
        var products = new List<Product>();

        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("sp_GetPaginatedProducts", connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            command.Parameters.AddWithValue("@PageNumber", pageNumber);
            command.Parameters.AddWithValue("@PageSize", pageSize);

            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    products.Add(new Product
                    {
                        Id = Convert.ToInt32(reader["Id"]),
                        Name = reader["Name"].ToString(),
                        Price = Convert.ToDecimal(reader["Price"])
                    });
                }
            }
        }

        return products;
    }
}

sp_GetPaginatedProducts (SQL Stored Procedure)

CREATE PROCEDURE sp_GetPaginatedProducts
    @PageNumber INT,
    @PageSize INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Id, Name, Price
    FROM Products
    ORDER BY Id
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END

Step 2. Implementing the Service and Controller.

ProductService.cs

public class ProductService
{
    private readonly ProductRepository _productRepository;

    public ProductService(ProductRepository productRepository)
    {
        _productRepository = productRepository;
    }

    public List<Product> GetPaginatedProducts(int pageNumber, int pageSize)
    {
        return _productRepository.GetPaginatedProducts(pageNumber, pageSize);
    }
}

ProductsController.cs

[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly ProductService _productService;

    public ProductsController(ProductService productService)
    {
        _productService = productService;
    }

    [HttpGet]
    public IActionResult Get(int pageNumber = 1, int pageSize = 10)
    {
        var products = _productService.GetPaginatedProducts(pageNumber, pageSize);
        return Ok(products);
    }
}

Step 3. Configuring Dependency Injection.

Program.cs

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllers();
builder.Services.AddSingleton(new ProductRepository(builder.Configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddScoped<ProductService>();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseDeveloperExceptionPage();
}

app.UseRouting();
app.UseEndpoints(endpoints =>
{
    endpoints.MapControllers();
});

app.Run();

Conclusion

Pagination is a critical feature for applications dealing with large datasets. This blog demonstrated how to implement pagination in a C#.NET application both with and without Entity Framework. While EF simplifies many tasks, using ADO.NET or other data access techniques provides greater control. Choose the approach that best fits your application’s needs and architecture.