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.
📌Explore more at: https://dotnet-fullstack-dev.blogspot.com/
🌟 Sharing would be appreciated! 🚀
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.
Register your DbContext and ProductService in Program.cs.
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 are 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.
Top comments (2)
Seriously, a stored procedure?! And then you need to maintain the code in two places: the database and the backend
@ngrigorev Obviously! Without EF, we should send PageNumber and PageSize to stored proc (DB) from the repository (Backend). Honestly, we will not maintain the same code in two places, we pass it from backend to the database