Introduction to Repository Pattern in ASP.NET Core Web API

Back to: ASP.NET Core Web API Tutorials

Introduction to Repository Pattern in ASP.NET Core Web API

In this article, I will briefly introduce the Repository Pattern in ASP.NET Core Web API. In modern application development, data access is a crucial layer interacting with the database to perform CRUD (Create, Read, Update, Delete) operations. ASP.NET Core Web API combined with Entity Framework Core (EF Core) provides a powerful and flexible way to manage data access efficiently.

First, we will introduce various data access techniques, such as ADO.NET Core, Dapper, and EF Core. We will also discuss the importance of abstraction using the Repository Pattern and demonstrate a practical real-time e-commerce application using EF Core Code First with SQL Server.

Overview of Data Access Techniques in Modern Applications

When building modern ASP.NET Core applications, efficiently accessing and manipulating data stored in databases is a fundamental requirement. Over the years, several data access techniques and technologies have evolved, each with its own strengths, trade-offs, and ideal use cases. The following are the primary data access approaches commonly used today:

ADO.NET Core

ADO.NET (ADO.NET Core) is the original data access framework for .NET, providing a low-level, manual way to interact with databases. Developers need to write raw SQL queries or stored procedure calls and execute them using classes like SqlConnection, SqlCommand, and SqlDataReader.

Advantages:

• High performance and full control over database operations.
• Suitable for scenarios requiring finely tuned SQL or bulk operations.

Drawbacks:

• Requires repetitive code to manage connections, commands, and data readers.
• Error-prone and harder to maintain due to manual mapping of query results to objects.
• Lacks built-in support for change tracking or complex object relationships.

Dapper

Dapper is a popular lightweight micro-ORM (Object-Relational Mapper) that builds on top of ADO.NET to simplify data access while preserving performance. Dapper maps SQL query results directly to strongly typed objects, reducing the need to write manual data mapping code.

Advantages:

• Minimal overhead and near-raw ADO.NET performance.
• Simple API for executing parameterized SQL and mapping to POCOs (Plain Old CLR Objects).
• Great choice when you want control over SQL but want to avoid manual mapping.

Drawbacks:

• Does not provide advanced ORM features like change tracking or migrations.
• Developers still write raw SQL queries, so complex business logic can become scattered.

Entity Framework Core (EF Core)

EF Core is a full-featured ORM developed by Microsoft that provides a high-level abstraction over database access. EF Core allows developers to work with database data as strongly typed .NET objects and supports two primary development approaches:

• Code First: Define your data model using C# classes, and EF Core generates the database schema and migrations.
• Database First: Scaffold model classes based on an existing database schema.

Features:

• LINQ-based querying for type-safe and expressive data retrieval.
• Change tracking to detect and apply updates automatically.
• Support for complex relationships, inheritance, and concurrency control.
• Migrations are used to evolve the database schema over time.

Advantages:

• Rapid development with minimal repetitive code.
• Strongly integrates with ASP.NET Core and supports dependency injection.
• Encourages maintainable, testable, and clean code architecture.

Drawbacks:

• Slightly more overhead compared to micro-ORMs and ADO.NET.
• It requires understanding how EF Core translates LINQ to SQL for performance tuning.

Why EF Core Is Preferred in Modern ASP.NET Core Web APIs

Given these options, EF Core is the most popular choice in modern .NET applications, especially when building scalable, maintainable Web APIs. Here’s why:

• Productivity: Developers spend less time writing repetitive data access code, focusing instead on business logic.
• Maintainability: Strongly typed models and LINQ queries reduce runtime errors and improve code clarity.
• Flexibility: Supports complex domain models with relationships, enabling richer applications.
• Ecosystem Integration: Seamlessly integrates with ASP.NET Core middleware, dependency injection, and logging frameworks.
• Migrations Support: Enables easy database schema evolution without manually handling SQL scripts.
• Community and Support: Backed by Microsoft and a large community, EF Core continuously improves with frequent updates.

For these reasons, this project adopts Entity Framework Core with the Code First approach as the foundation for data access. EF Core accelerates development and pairs well with the Repository Pattern, an architectural design that abstracts data access logic behind interfaces, enhancing testability and separation of concerns.

Where Does the Repository Fit in a Layered Architecture?

A best layered architecture looks like Controller ➔ Service Layer ➔ Repository ➔ DbContext ➔ SQL Server, as shown in the image below.

The above diagram illustrates a common and effective layered architecture pattern used in modern ASP.NET Core Web API applications. This architecture cleanly separates concerns, promoting maintainability, testability, and scalability by dividing the application into distinct layers, each responsible for specific tasks.

Presentation Layer (API Controllers)

This is the outermost layer that handles all incoming HTTP requests from clients (e.g., browsers, mobile apps). The following are the key Responsibilities of the Presentation Layer:

• Accept HTTP requests and route them to appropriate actions.
• Validate request data (basic validation).
• Return HTTP responses with status codes and data.
• Does not contain business logic or data access code.

Example: ASP.NET Core API Controllers, which use attributes like [HttpGet], [HttpPost] to expose API endpoints.

Service Layer (Business Logic)

The core layer that contains the business rules and workflows of the application. This layer keeps business logic separate from controllers and data access, making it easier to modify and test. The following are the key Responsibilities of the Service Layer:

• Implements business operations, validations, and calculations.
• Manage complex tasks, possibly involving multiple repositories.
• Acts as an intermediary between the Presentation Layer and the Repository Layer.

Example: Services like OrderService, ProductService that perform operations like order validation, discount calculations, and inventory checks.

Repository Layer (Abstraction over EF Core)

Provides an abstraction over the data access logic using the Repository Pattern. This Layer decouples business logic from data access technology. It simplifies switching data access methods if needed in the future. The following are the key Responsibilities of the Repository Layer:

• Defines interfaces and classes that encapsulate all data-related operations.
• Hides the underlying details of EF Core and database queries from higher layers.
• Allows for mocking during unit testing by providing interfaces.

Example: Interfaces like IProductRepository with methods like GetProductById(), AddProduct(), implemented with EF Core.

Data Access Layer (DbContext, Entities)

This is the EF Core DbContext layer that acts as the gateway to the database. The following are the key Responsibilities of the Data Access Layer:

• Manages entity objects and tracks their state.
• Translates LINQ queries from repositories into SQL queries.
• Handles change tracking, relationship management, and concurrency.
• Executes SQL commands against the database.

Example: Your ECommerceDbContext class is derived from DbContext with DbSet<T> properties representing tables.

SQL Server (Database)

The persistent storage layer where all application data is stored. The following are the key Responsibilities of SQL Server Database:

• Stores tables, indexes, stored procedures, and data.
• Ensures data integrity, security, and performance.
• Processes SQL queries generated by EF Core.

Example: Microsoft SQL Server instance hosting the ECommerceDB database.

Why Use This Architecture?

With the above Layered Architecture, we will get the following benefits:

• Separation of Concerns: Each layer is responsible for a specific part of the application, making code easier to understand, test, and maintain.
• Testability: You can test business logic or controllers in isolation by mocking dependencies (services or repositories).
• Scalability and Flexibility: It’s easy to swap out or extend layers (e.g., change from SQL Server to MongoDB in the repository) with minimal impact on the rest of the system.
• Maintainability: It is easier to update one layer without affecting others. It is very simple to swap or upgrade technologies (e.g., change EF Core to Dapper) by modifying only the repository layer.

Why Use the Repository Pattern in ASP.NET Core Web API?

When building ASP.NET Core Web API applications, one common approach to data access is to directly inject the Entity Framework Core’s DbContext into controllers and use it to perform CRUD operations. While this approach can be straightforward and quick for very small or simple applications, it has several significant drawbacks as the application grows in complexity.

Problems with Directly Using DbContext in Controllers

Let us first understand the problems with Directly Using DbContext in Controllers, and then we will discuss how the Repository Pattern overcomes these problems.

Tight Coupling Between Controller and EF Core:

When controllers directly depend on DbContext, they become tightly coupled to EF Core’s implementation details. This means that any changes in how data is accessed or stored (e.g., switching to a different ORM or database) require changes in the controller code. This violates the Dependency Inversion Principle of SOLID design, which promotes depending on abstractions rather than concrete implementations.

Code Duplication Across Controllers:

Without a centralized data access layer, common database operations (like fetching, inserting, and updating entities) often get duplicated across multiple controllers. This duplication leads to repetitive code that’s harder to maintain and more prone to bugs.

Harder Unit Testing:

Unit testing controllers becomes challenging because you have to mock DbContext, which is a complex class tightly coupled with EF Core internals. Mocking DbContext and its DbSet properties can be tedious and error-prone, often requiring in-memory databases or other workarounds. This reduces test reliability and slows down development.

Hard-to-Maintain Codebase:

Mixing data access logic directly into controllers or services results in a complex codebase. It becomes difficult to isolate, modify, or extend data access without unintended side effects. This makes future enhancements or refactoring risky and time-consuming.

Benefits of Using the Repository Pattern:

The Repository Pattern solves these problems by introducing an abstraction layer between our business logic and the data access technology.

Separation of Concerns:

The repository acts as a dedicated data access layer. Controllers and services interact with repositories, which expose simple, business-centric interfaces like GetById(), Add(), Update(), and Delete(). This ensures business logic is decoupled from the details of EF Core or SQL.

Loosely Coupled Architecture:

By depending on repository interfaces rather than concrete implementations, our application follows the Dependency Inversion Principle. This loose coupling makes it easier to swap out the underlying data access layer without affecting the business or presentation layers. For example, you could replace EF Core with Dapper or mock repositories for testing.

Improved Testability:

Repositories are simple interfaces that can be easily mocked in unit tests. This enables fast, isolated testing of business logic and controllers without involving the database or complex DbContext mocks.

Centralized Data Access Code:

All data access logic is concentrated in repository classes. This centralization reduces duplication and makes it easier to apply changes (e.g., add caching, logging, or query optimizations) in one place. It also improves consistency and helps enforce best practices across the application.

While injecting DbContext directly into controllers may be tempting for quick development, the Repository Pattern provides a structured, maintainable approach suitable for medium to large applications. It promotes clean separation of concerns, loose coupling, testability, and maintainability, key qualities of robust and scalable software architecture.

Important Note: In this chapter, we will focus on setting up a new ASP.NET Core Web API Project with EF Core Code-First Approach, designing a normalized schema, configuring DbContext, and seeding initial data. Later chapters will build on this foundation to introduce the Repository Pattern.

Real-Time Scenario: E-Commerce Product Management System

Imagine you are building a real-time E-Commerce Web API to manage products, categories, customers, and orders. This system needs scalable data access, clean separation of concerns, and maintainability. So, we will build a simple ECommerce API with the following entities:

• Category: Groups products into logical categories (e.g., Electronics, Apparel, etc.).
• Product: Items for sale, belonging to a category.
• Customer: End users who place orders.
• Order: A purchase made by a customer.
• OrderItem: Details of each product in an order.

Setting Up the Project and Installing Entity Framework Core

First, create a new ASP.NET Core Web API Application named ECommerceAPI. Once you have created the project, please install the Entity Framework Core Packages by executing the following commands in the Package Manager Console.

• Install-Package Microsoft.EntityFrameworkCore.SqlServer
• Install-Package Microsoft.EntityFrameworkCore.Tools

Configuring SQL Server Connection

Next, please update the appsettings.json with the database connection string as follows:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "ECommerceDBConnection": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=ECommerceDB;Trusted_Connection=True;TrustServerCertificate=True;"
  }
}

Defining Domain Models

First, create a new folder named Models in the Project root directory where we will define all our entities:

Category

Create a class file named Category.cs within the Models folder and then copy and paste the following code. The Category entity helps organize products into logical groups for easier navigation and filtering. For example, Electronics, Apparel, Books, Home Appliances. It helps clients filter products by category.

using System.ComponentModel.DataAnnotations;
namespace ECommerceAPI.Models
{
    public class Category
    {
        [Key]
        public int CategoryId { get; set; }

        [Required(ErrorMessage = "Category name is required.")]
        [StringLength(100, ErrorMessage = "Category name cannot exceed 100 characters.")]

        public string Name { get; set; } = string.Empty;

        [StringLength(500, ErrorMessage = "Description cannot exceed 500 characters.")]

        public string? Description { get; set; }
        public ICollection<Product>? Products { get; set; }
    }
}

Product

Create a class file named Product.cs within the Models folder and then copy and paste the following code. The Product entity represents the actual items available for purchase on the e-commerce platform. For example, Product name, description, price, stock quantity, and a foreign key to the category it belongs to. It also includes business rules like price validation, inventory checks. Each Product belongs to exactly one category, establishing a relationship for filtering.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ECommerceAPI.Models
{
    public class Product
    {
        [Key]
        public int ProductId { get; set; }

        [Required(ErrorMessage = "Product name is required.")]
        [StringLength(150, ErrorMessage = "Product name cannot exceed 150 characters.")]
        public string Name { get; set; } = string.Empty;

        [StringLength(1000, ErrorMessage = "Description cannot exceed 1000 characters.")]
        public string? Description { get; set; }

        [Required(ErrorMessage = "Price is required.")]
        [Range(0.01, 999999.99, ErrorMessage = "Price must be between 0.01 and 999,999.99")]
        [Column(TypeName = "decimal(12,2)")]
        public decimal Price { get; set; }

        [ForeignKey(nameof(Category))]
        [Required(ErrorMessage = "CategoryId is required.")]
        public int CategoryId { get; set; }

        public Category Category { get; set; } = null!;

        public List<OrderItem> OrderItems { get; set; } = new List<OrderItem>();
    }
}

Customer

Create a class file named Customer.cs within the Models folder and then copy and paste the following code. The Customer entity represents the users or buyers who can browse products and place orders. For example, Name, email, address, contact details, etc. It handles registration, authentication, and profile management. Tracks customer order history and preferences.

using System.ComponentModel.DataAnnotations;
namespace ECommerceAPI.Models
{
    public class Customer
    {
        [Key]
        public int CustomerId { get; set; }

        [Required(ErrorMessage = "Full name is required.")]
        [StringLength(200, ErrorMessage = "Full name cannot exceed 200 characters.")]
        public string FullName { get; set; } = string.Empty;

        [Required(ErrorMessage = "Email is required.")]
        [EmailAddress(ErrorMessage = "Invalid email address format.")]
        public string Email { get; set; } = null!;

        public ICollection<Order>? Orders { get; set; }
    }
}

Order

Create a class file named Order.cs within the Models folder and then copy and paste the following code. The Order entity represents a purchase transaction made by a customer. For example, the Order date, total amount, status, and foreign key to the customer. It manages the order lifecycle (e.g., placed, shipped, delivered). Tracks payment status and shipment details. Aggregates multiple products ordered by the customer.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ECommerceAPI.Models
{
    public class Order
    {
        [Key]
        public int OrderId { get; set; }

        [Required(ErrorMessage = "Order date is required.")]
        public DateTime OrderDate { get; set; }

        [ForeignKey(nameof(Customer))]
        [Required(ErrorMessage = "CustomerId is required.")]
        public int CustomerId { get; set; }

        [Required(ErrorMessage = "Order amount is required.")]
        [Range(0.0, 99999999.99, ErrorMessage = "Order amount must be positive.")]
        [Column(TypeName = "decimal(12,2)")]
        public decimal OrderAmount { get; set; }

        public Customer Customer { get; set; } = null!;
        public ICollection<OrderItem> OrderItems { get; set; } = new List<OrderItem>();
    }
}

OrderItem

Create a class file named OrderItem.cs within the Models folder and then copy and paste the following code. The OrderItem entity represents each product within an order. For example, Quantity, unit price, foreign keys to order, and product. It breaks down an order into individual products. It also enables inventory deduction per product ordered.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ECommerceAPI.Models
{
    public class OrderItem
    {
        [Key]
        public int OrderItemId { get; set; }

        [ForeignKey(nameof(Order))]
        [Required(ErrorMessage = "OrderId is required.")]
        public int OrderId { get; set; }

        public Order Order { get; set; } = null!;

        [ForeignKey(nameof(Product))]
        [Required(ErrorMessage = "ProductId is required.")]
        public int ProductId { get; set; }

        public Product Product { get; set; } = null!;

        [Required(ErrorMessage = "Quantity is required.")]
        [Range(1, 10000, ErrorMessage = "Quantity must be at least 1.")]
        public int Quantity { get; set; }

        [Required(ErrorMessage = "Unit price is required.")]
        [Range(0.01, 999999.99, ErrorMessage = "Unit price must be between 0.01 and 999,999.99")]
        [Column(TypeName = "decimal(12,2)")]
        public decimal UnitPrice { get; set; }
    }
}

Configuring the DbContext

First, create a new folder named Data in the Project root directory. Then, inside the Data folder, create a class file named ECommerceDBContext.cs and copy and paste the following code. The class is inherited from the EF Core DbContext class.

using ECommerceAPI.Models;
using Microsoft.EntityFrameworkCore;

namespace ECommerceAPI.Data
{
    public class ECommerceDbContext : DbContext
    {
        public ECommerceDbContext(DbContextOptions<ECommerceDbContext> options)
            : base(options)
        {
        }

        public DbSet<Category> Categories { get; set; }
        public DbSet<Product> Products { get; set; }
        public DbSet<Customer> Customers { get; set; }
        public DbSet<Order> Orders { get; set; }
        public DbSet<OrderItem> OrderItems { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            //Defining the Relationship beween Order and OrderItems with Cascade Delete Behavior
            modelBuilder.Entity<Order>()
            .HasMany(o => o.OrderItems)
            .WithOne(oi => oi.Order)
            .HasForeignKey(oi => oi.OrderId)
            .OnDelete(DeleteBehavior.Cascade);

            // Seed Categories
            modelBuilder.Entity<Category>().HasData(
                new Category { CategoryId = 1, Name = "Electronics", Description = "Electronic devices and gadgets" },
                new Category { CategoryId = 2, Name = "Books", Description = "Various genres of books and literature" },
                new Category { CategoryId = 3, Name = "Clothing", Description = "Men's and women's apparel" }
            );

            // Seed Products
            modelBuilder.Entity<Product>().HasData(
                new Product { ProductId = 1, Name = "Laptop", Price = 1500m, CategoryId = 1, Description = "High performance laptop" },
                new Product { ProductId = 2, Name = "Smartphone", Price = 800m, CategoryId = 1, Description = "Latest model smartphone" },
                new Product { ProductId = 3, Name = "ASP.NET Core Book", Price = 40m, CategoryId = 2, Description = "Comprehensive guide to ASP.NET Core" }
            );

            // Seed Customers
            modelBuilder.Entity<Customer>().HasData(
                new Customer { CustomerId = 1, FullName = "John Doe", Email = "john@example.com" },
                new Customer { CustomerId = 2, FullName = "Jane Smith", Email = "jane@example.com" }
            );

            // Seed Orders
            modelBuilder.Entity<Order>().HasData(
                new Order { OrderId = 1, CustomerId = 1, OrderDate = new DateTime(2024, 1, 15, 10, 30, 0), OrderAmount = 1540.00m },
                new Order { OrderId = 2, CustomerId = 2, OrderDate = new DateTime(2024, 2, 5, 15, 45, 0), OrderAmount = 840.00m }
            );

            // Seed OrderItems
            modelBuilder.Entity<OrderItem>().HasData(
                new OrderItem { OrderItemId = 1, OrderId = 1, ProductId = 1, Quantity = 1, UnitPrice = 1500.00m },
                new OrderItem { OrderItemId = 2, OrderId = 1, ProductId = 3, Quantity = 1, UnitPrice = 40.00m },
                new OrderItem { OrderItemId = 3, OrderId = 2, ProductId = 2, Quantity = 1, UnitPrice = 800.00m },
                new OrderItem { OrderItemId = 4, OrderId = 2, ProductId = 3, Quantity = 1, UnitPrice = 40.00m }
            );
        }
    }
}

Dependency Injection & DbContext Lifecycle

ASP.NET Core comes with built-in Dependency Injection (DI), which is a design pattern that allows us to inject (or provide) required dependencies into classes rather than hard-coding them inside those classes. This promotes loose coupling, testability, and maintainability. In our Program.cs file, we need to register ECommerceDbContext as a service with the DI container. So, please modify the Program class as follows:

using ECommerceAPI.Data;
using Microsoft.EntityFrameworkCore;
namespace ECommerceAPI
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var builder = WebApplication.CreateBuilder(args);

            //Register the Services
            builder.Services.AddControllers()
             .AddJsonOptions(options =>
             {
                 // Disabled Camel Case in JSON serialization and deserialization
                 options.JsonSerializerOptions.PropertyNamingPolicy = null;
             });

            // Register ECommerceDbContext
            // By default it will be a Scopped Service
            builder.Services.AddDbContext<ECommerceDbContext>(options =>
                options.UseSqlServer(builder.Configuration.GetConnectionString("ECommerceDBConnection"))
            );

            builder.Services.AddEndpointsApiExplorer();
            builder.Services.AddSwaggerGen();

            var app = builder.Build();

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

            app.UseHttpsRedirection();

            app.UseAuthorization();

            app.MapControllers();

            app.Run();
        }
    }
}

Code Explanations:

Whenever a class (like a controller or service) declares a dependency on ECommerceDbContext in its constructor, ASP.NET Core automatically provides an instance of it. This avoids manually creating DbContext instances and ensures proper lifecycle management.

DbContext Lifetime: Scoped

The ECommerceDbContext is registered with a Scoped lifetime by default in ASP.NET Core. The Scoped lifetime means one instance of ECommerceDbContext per HTTP request. This is the default and recommended lifetime for DbContext because:

• It prevents concurrency issues if the DbContext is shared across multiple threads.
• Each web request gets a fresh DbContext instance, ensuring data consistency and safe change tracking.
• It efficiently manages database connections by opening them only when needed within the request lifecycle.

Other lifetimes:

• Transient: Creates a new instance every time requested (not recommended for DbContext due to possible overhead and connection management issues).
• Singleton: One instance for the entire application lifetime (not suitable for DbContext because it’s not thread-safe).

JSON Serialization Configuration

In the same Program.cs class file, we disable camel case naming in JSON serialization. ASP.NET Core by default converts C# property names (PascalCase) to camelCase in JSON (e.g., ProductName becomes productName).

• Some APIs or clients may require JSON property names to exactly match the C# property names (PascalCase), or to follow a specific contract.
• Disabling this ensures JSON input/output matches the property names exactly as declared in the C# models.

How Dependency Injection Works in Real-time:

Constructor Injection: Any controller or service needing to perform database operations declares a constructor parameter of type ECommerceDbContext. For Example:

public class ProductsController : ControllerBase
{
    private readonly ECommerceDbContext _context;

    public ProductsController(ECommerceDbContext context)
    {
        _context = context;
    }
}

ASP.NET Core Provides a DbContext Instance: The DI container automatically injects the scoped instance of ECommerceDbContext when the controller is instantiated.

Scoped Lifetime Ensures Isolation: Each HTTP request gets its own DbContext instance, which is disposed of after the request completes, freeing resources.

Applying EF Core Migrations and Database Creation

Run the following commands in the Package Manager Console:

• Add-Migration Mig1
• Update-Database

This will create the database with tables and seed the initial data. It should create the ECommerceDB database in SQL Server with the following tables.

In this chapter, we have:

• Introduced data access techniques and the importance of abstraction.
• Designed an ECommerce database schema with multiple entities.
• Implemented EF Core Code First with model classes.
• Seeded initial data using OnModelCreating.
• Configured the SQL Server connection.
• Explained DbContext lifecycle and dependency injection in ASP.NET Core.

The next step will be to demonstrate CRUD operations without any repository abstraction to highlight the limitations of tight coupling before we introduce the Repository Pattern for clean, maintainable data access.

In the next article, I will discuss Starting an Application Without Using the Repository Pattern in ASP.NET Core Web API. In this article, I explain the Repository Pattern in an ASP.NET Core Web API Application with Examples. I hope you enjoy this article on the Repository Pattern in ASP.NET Core Web API.