How to Pagination in .NET: With and Without Entity Framework?

The technique of breaking up a big dataset into more manageable, smaller pieces that may be fetched and shown gradually is known as pagination. This method is crucial for enhancing the user experience and performance of applications, particularly when working with big 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.
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’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.

Best ASP.NET 9.0 Core Hosting

The feature and reliability are the most important things when choosing a good ASP.NET Core hosting. HostForLIFE is the leading provider of Windows hosting and affordable ASP.NET Core , their servers are optimized for PHP web applications such as the latest ASP.NET Core version. The performance and the uptime of the ASP.NET Core hosting service are excellent, and the features of the web hosting plan are even greater than what many hosting providers ask you to pay for. At HostForLIFEASP.NET, customers can also experience fast ASP.NET Core hosting. The company invested a lot of money to ensure the best and fastest performance of the datacenters, servers, network and other facilities. Its data centers are equipped with top equipment like cooling system, fire detection, high-speed Internet connection, and so on. That is why HostForLIFEASP.NET guarantees 99.9% uptime for ASP.NET Core . And the engineers do regular maintenance and monitoring works to assure its ASP.NET Core hosting are security and always up.