Database Migration Strategies — Complete Guide

Introduction

Database Migration Strategies — 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 database migration strategies 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 Database Migration Strategies in plain English and in SQL Server and high-performance data access terms
• Implement database migration strategies 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 86 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 84 — CI/CD for Database Projects — Complete Guide
• Time: 28 min reading + 30–45 min hands-on

Concept deep-dive

Level 1 — Analogy

EF Core is a translator between C# objects and SQL Server — you speak C#; it writes SQL the database understands.

Level 2 — Technical

Database Migration Strategies 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 — Database Migration Strategies 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 Database Migration Strategies

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

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

SQL performance and connection management — Database Migration Strategies

• 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 — Razorpay-Style Payment Reconciliation

MANDATORY enterprise scenario (Payment Gateway): Database Migration Strategies in ShopNest.DataAccess Inventory.

Business problem

End-of-day reconciliation matches 1M gateway transactions against internal ledger. ADO.NET table-valued parameters feed set-based MERGE in SQL Server — impossible to express efficiently in LINQ.

Architecture

Gateway CSV → TVP dbo.TransactionBatch → usp_ReconcilePayments
  → MERGE Payments.Ledger → Output mismatches to ReconciliationExceptions

Production ADO.NET code

var tvp = new SqlParameter("@Batch", SqlDbType.Structured)
{
    TypeName = "dbo.TransactionBatchType",
    Value = BuildDataTable(transactions)
};
cmd.Parameters.Add(tvp);
await cmd.ExecuteNonQueryAsync(ct);

Outcome

Reconciliation completes in 12 minutes; EF Core prototype timed out at 45 minutes on same hardware.

Real-World Example 2 — TCS ERP Monthly GL Reporting

MANDATORY enterprise scenario (Enterprise ERP): Database Migration Strategies 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.

ADO.NET with ASP.NET Core — Database Migration Strategies

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 Database Migration Strategies in ASP.NET Core MVC?
A: Database Migration Strategies 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 Database Migration Strategies 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 Database Migration Strategies for ShopNest Inventory: show interface, concrete class, DI registration, and xUnit test with mock.

public class DatabaseMigrationStrategiesPatternTests
{
    [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 85: Database Migration Strategies — Complete Guide
• Module: Module 9: Cloud and DevOps · Level: ADVANCED
• Applied to ShopNest.DataAccess — Inventory

Previous: CI/CD for Database Projects — Complete Guide
Next: Backup and Restore — Complete Guide

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

FAQ