Repository Pattern — Complete Guide

Introduction

Repository Pattern — Complete Guide is essential for .NET architects building ShopNest.DataAccess — Enterprise High-Performance Data Platform — Toolliyo's 100-article ADO.NET Core master path covering SqlConnection, stored procedures, transactions, connection pooling, performance tuning, ASP.NET Core integration, and senior interview preparation. Every article includes minimum 2 detailed enterprise real-world examples (banking transfers, ERP reporting, insurance batch, legacy modernization) in different business domains.

In Indian delivery projects (TCS, Infosys, Wipro), interviewers expect repository pattern with real ICICI-style banking, TCS ERP reporting, insurance batch processing, or government legacy modernization examples — not toy animal demos. This article delivers two mandatory enterprise examples on Inventory.

After this article you will

• Explain Repository Pattern in plain English and in SQL Server and high-performance data access terms
• Implement repository pattern in ShopNest.DataAccess — Enterprise High-Performance Data Platform (Inventory)
• Compare the wrong approach vs the production-ready enterprise approach
• Answer fresher, mid-level, and senior ADO.NET and SQL Server interview questions confidently
• Connect this lesson to Article 54 and the 100-article ADO.NET Core roadmap

Prerequisites

• Software: .NET 8 SDK, VS 2022 or VS Code, SQL Server Express / LocalDB
• Knowledge: C# basics
• Previous: Article 52 — ADO.NET with ASP.NET Core Web API — Complete Guide
• Time: 28 min reading + 30–45 min hands-on

Concept deep-dive

Level 1 — Analogy

Repository Pattern on ShopNest.DataAccess adds high-performance SQL Server data access for enterprise modules.

Level 2 — Technical

Repository Pattern integrates with the LINQ query layer: write queries against IEnumerable or IQueryable, understand deferred execution, project to DTOs for ShopNest.DataAccess reports. On ShopNest.DataAccess this powers Inventory without coupling UI to database internals.

Level 3 — Architecture

[Browser] → [HTTPS/Kestrel] → [Middleware Pipeline]
  → [Routing] → [Controller Action] → [Service Layer]
  → [EF Core / Identity] → [Razor View Engine] → [HTML Response]

Project structure

ShopNest.DataAccess/
├── ShopNest.DataAccess/
├── src/
│   ├── ShopNest.DataAccess.Api/       ← ASP.NET Core Web API
│   ├── ShopNest.DataAccess.Core/      ← Repository interfaces
│   ├── ShopNest.DataAccess.AdoNet/    ← SqlConnection, SPs, transactions
│   ├── ShopNest.DataAccess.Reports/   ← Streaming readers, GL reports
│   └── ShopNest.DataAccess.Tests/     ← Integration tests (Testcontainers SQL)
├── sql/
│   ├── migrations/
│   └── stored-procedures/
└── docker-compose.yml                 ← SQL Server 2022 + Redis

Step-by-Step Implementation — ShopNest (Inventory)

Follow the prompt template: create project → core classes → interfaces → pattern implementation → client code → run → enterprise refactor.

Step 1 — The wrong way

// ❌ BAD — fat controller, no ViewModel, sync DB call
public IActionResult Index()
{
    return _context.Products.Find(id); // sync, exposes entity, no auth
}

Step 2 — The right way

// ✅ CORRECT — Repository Pattern on ShopNest (Inventory)
var results = await _context.Products
    .Where(p => p.IsPublished && p.CategoryId == categoryId)
    .OrderBy(p => p.Name)
    .Select(p => new ProductReportDto { Id = p.Id, Name = p.Name, Revenue = p.Orders.Sum(o => o.Total) })
    .ToListAsync(ct);

Step 3 — Apply Repository Pattern

await _context.Products
    .Where(p => p.IsPublished)
    .OrderBy(p => p.Name)
    .ToListAsync();

dotnet run --project ShopNest.DataAccess.Api
# Verify Repository Pattern — check SQL Server Management Studio and connection pool metrics and integration tests pass

SQL performance and connection management — Repository Pattern

• Connection pooling — default enabled; never disable without load testing; watch pool exhaustion (error 10053/10054)
• Parameterized queries — always use SqlParameter; prevents SQL injection and enables plan cache reuse
• Async — ExecuteReaderAsync/ExecuteNonQueryAsync free thread pool under load
• CommandBehavior.SequentialAccess — stream large BLOB/text columns without loading full row into memory
• Indexes — align with WHERE/JOIN columns; use SQL Server DMVs to find missing indexes

Real-World Example 1 — TCS ERP Monthly GL Reporting

MANDATORY enterprise scenario (Enterprise ERP): Repository Pattern in ShopNest.DataAccess Inventory.

Business problem

Finance teams run month-end General Ledger reports across 200+ cost centers. Report queries join 12 tables and return 2M rows — EF Core materializes entire graphs into memory. ADO.NET SqlDataReader streams rows to CSV/PDF generators with constant memory.

Architecture

[Report Scheduler] → [GlReportRepository]
  → EXEC usp_GenerateMonthlyGL @Year, @Month, @CostCenterId
  → SqlDataReader forward-only stream → IAsyncEnumerable
  → Bulk copy to staging → SSRS / Excel export
Read uncommitted avoided; NOLOCK only on read replica for analytics.

Production ADO.NET code

public async IAsyncEnumerable<GlLineDto> StreamGlReportAsync(int year, int month, [EnumeratorCancellation] CancellationToken ct)
{
    await using var conn = new SqlConnection(_readReplicaConnectionString);
    await conn.OpenAsync(ct);
    await using var cmd = new SqlCommand("usp_GenerateMonthlyGL", conn)
    {
        CommandType = CommandType.StoredProcedure
    };
    cmd.Parameters.Add("@Year", SqlDbType.Int).Value = year;
    cmd.Parameters.Add("@Month", SqlDbType.Int).Value = month;

    await using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess, ct);
    while (await reader.ReadAsync(ct))
    {
        yield return new GlLineDto(
            reader.GetString(0),
            reader.GetDecimal(1),
            reader.GetDateTime(2));
    }
}

Outcome

Memory flat at 80MB for 2M-row report vs 1.2GB EF Core ToList(); report runtime cut from 14 min to 3 min.

Real-World Example 2 — Government Pension Legacy Modernization

MANDATORY enterprise scenario (Government / PSU): Repository Pattern in ShopNest.DataAccess Inventory.

Business problem

20-year-old VB6 + inline SQL pension system migrated to ASP.NET Core. ADO.NET wraps existing stored procedures — no ORM rewrite — minimizing regression risk while adding connection pooling and async I/O.

Architecture

Legacy SPs (unchanged) ← AdoNetLegacyBridge → ASP.NET Core Web API
  → Repository per bounded context (Pension, Disbursement, Audit)
  → Strangler Fig: new modules EF Core, legacy modules ADO.NET

Production ADO.NET code

// Bridge pattern — call legacy SP by name
public async Task<PensionDetail?> GetPensionAsync(string pensionId, CancellationToken ct)
{
    const string sql = "EXEC dbo.usp_GetPensionDetail @PensionId";
    await using var conn = new SqlConnection(_legacyConnStr);
    await using var cmd = new SqlCommand(sql, conn);
    cmd.Parameters.Add("@PensionId", SqlDbType.VarChar, 15).Value = pensionId;
    await conn.OpenAsync(ct);
    await using var r = await cmd.ExecuteReaderAsync(ct);
    return r.Read() ? MapPension(r) : null;
}

Outcome

Migration delivered in 8 months vs 24-month full rewrite estimate; 99.2% SP compatibility on day one.

ADO.NET with ASP.NET Core — Repository Pattern

Register IOrderRepository implementations in DI as Scoped. Never hold SqlConnection across requests. Use IConfiguration for connection strings; User Secrets locally, Azure Key Vault in production.

builder.Services.AddScoped();
builder.Services.AddHealthChecks().AddSqlServer(connectionString);

Stored procedures and SQL safety

Enterprise ShopNest modules use versioned stored procedures (usp_ prefix). Never concatenate user input — always SqlParameter. Log slow queries (>500ms) with Serilog.

Database design

Product (Id, Name, Price, CategoryId)
Category (Id, Name)
Order (Id, CustomerId, OrderDate, Total)
OrderItem (OrderId, ProductId, Quantity, UnitPrice)

Use FK constraints, indexes on CategoryId and CustomerId, and avoid SELECT * in production LINQ queries.

Common errors & fixes

🔴 Mistake 1: Fat controllers with EF Core queries inline
✅ Fix: Move data access to services/repositories; keep controllers thin.

🔴 Mistake 2: Calling .ToList() too early materializing millions of rows into memory
✅ Fix: Defer execution — build IQueryable pipeline, then ToListAsync() once at the end.

🔴 Mistake 3: Filtering in memory after .ToList() instead of in the database query
✅ Fix: Keep filters in IQueryable, use Select projection, paginate with Skip/Take before materialization.

🔴 Mistake 4: Hard-coding connection strings in controllers
✅ Fix: Use appsettings.json + User Secrets locally; Azure Key Vault in production.

Best practices

• 🟢 Use async/await end-to-end for database and I/O calls
• 🟢 Register DbContext as Scoped; avoid capturing it in singletons
• 🟡 Use IQueryable until the last moment; avoid multiple enumeration; project with Select before ToList
• 🟡 Prefer method syntax for complex chains; use query syntax for joins when readability wins
• 🔴 Log structured data with Serilog — include OrderId, UserId, not passwords
• 🔴 Use HTTPS, secure cookies, and authorization policies in production

Interview questions

Fresher level

Q1: What is Repository Pattern in ASP.NET Core MVC?
A: Repository Pattern is a core MVC capability used in ShopNest.DataAccess for Inventory. Explain in one sentence, then describe controller/view/service placement.

Q2: How would you implement Repository Pattern on a TCS-style delivery project?
A: Deferred execution, IQueryable pipelines, Select projection, Skip/Take pagination, and SQL logging in development.

Q3: IEnumerable vs IQueryable — when to use which?
A: IEnumerable for in-memory collections; IQueryable for EF Core database queries that translate to SQL.

Mid / senior level

Q4: Explain LINQ deferred execution and query translation briefly.
A: LINQ → Expression Tree → IQueryProvider → SQL (EF) or Iterator (in-memory) → Results.

Q5: Common production mistake with this topic?
A: Skipping validation, exposing secrets in Git, or untested edge cases (null model, unauthorized user).

Q6: .NET LINQ vs SQL — when to push logic to database?
A: Core is cross-platform, faster, cloud-ready; Framework is maintenance mode on Windows/IIS.

Coding round

Implement Repository Pattern for ShopNest Inventory: show interface, concrete class, DI registration, and xUnit test with mock.

public class RepositoryPatternTests
{
    [Fact]
    public async Task ExecuteAsync_ReturnsSuccess()
    {
        var mock = new Mock();
        mock.Setup(s => s.ExecuteAsync(It.IsAny(), default))
            .ReturnsAsync(Result.Success("test-id"));
        var result = await mock.Object.ExecuteAsync(new Request("test-id"));
        Assert.True(result.IsSuccess);
    }
}

Summary & next steps

• Article 53: Repository Pattern — Complete Guide
• Module: Module 6: ASP.NET Core Integration · Level: ADVANCED
• Applied to ShopNest.DataAccess — Inventory

Previous: ADO.NET with ASP.NET Core Web API — Complete Guide
Next: Clean Architecture — Complete Guide

Practice: Add one small feature using today's pattern — commit with feat(adonet): article-53.

FAQ