Transaction Optimization in EF Core

Introduction

Transaction Optimization in EF Core is essential for .NET developers building the data layer of ShopNest.Data — Toolliyo's 100-article EF Core path covering DbContext, Code First, migrations, relationships, LINQ, performance, transactions, and enterprise patterns for SQL Server and cloud deployments.

In Indian delivery projects, teams lose sprints when juniors skip transaction optimization fundamentals — N+1 queries, missing indexes, sync database calls, or untested migrations. This article prevents that on Orders.

After this article you will

• Explain Transaction Optimization in plain English and in technical EF Core ORM terms
• Implement transaction optimization in ShopNest.Data (Orders)
• Compare the wrong approach vs the production-ready enterprise approach
• Answer fresher and mid-level EF Core interview questions confidently
• Connect this lesson to Article 69 and the 100-article EF Core roadmap

Prerequisites

• Software: .NET 8 SDK, VS 2022 or VS Code, SQL Server Express / LocalDB
• Knowledge: C# basics
• Previous: Article 67 — Connection Pooling in EF Core
• Time: 28 min reading + 30–45 min hands-on

Concept deep-dive

Level 1 — Analogy

Transaction Optimization in EF Core connects entity modeling, SQL translation, and change tracking for ShopNest.Data.

Level 2 — Technical

Transaction Optimization handles consistency and performance — wrap multi-step writes in transactions and use raw SQL only where LINQ cannot express the operation cleanly.

Level 3 — EF Core data flow

[Application Service / API]
       ▼
[DbContext (Scoped)]
       ▼
[LINQ → Expression Tree → SQL Generator]
       ▼
[SQL Server / PostgreSQL]
       ▼
[Data Reader → Materialization → Change Tracker]
       ▼
[DTO Projection / SaveChangesAsync]

Project structure

ShopNest.Data/
├── ShopNest.Domain/           ← Entity classes
├── ShopNest.Infrastructure/   ← DbContext, configurations, migrations
├── ShopNest.Application/      ← Services, repository interfaces
├── ShopNest.Api/              ← ASP.NET Core host (optional)
└── ShopNest.Tests/            ← Integration tests (SQLite/InMemory)

Hands-on implementation — Orders

Follow the steps below to practice Transaction Optimization in Orders with a minimal working example.

1. Read the lesson objective and list success criteria.
2. Implement the smallest working version.
3. Test happy path and one failure case.
4. Compare your code to the good example below.
5. Note one interview talking point from what you built.

Anti-pattern (quick hack without tests or error handling)

// ❌ BAD — N+1 queries, sync IO, tracked entities returned to API
foreach (var orderId in orderIds)
{
    var order = _context.Orders.Find(orderId); // sync + N round-trips
    dto.Add(Map(order)); // exposes tracked entity graph
}

Production-style example

// ✅ CORRECT — Transaction Optimization on ShopNest (Orders)
public async Task<ProductDto?> GetDtoAsync(int id, CancellationToken ct)
{
    return await _context.Products.AsNoTracking()
        .Where(p => p.Id == id)
        .Select(p => new ProductDto { Id = p.Id, Name = p.Name })
        .FirstOrDefaultAsync(ct);
}

Complete example

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

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

• N+1 queries in loops — Use Include, projection, or explicit loading.
• Tracking large graphs — Use AsNoTracking for read-only queries.
• Ignoring migration reviews — Review generated SQL before applying to production.

Best practices

• 🟢 Register DbContext as Scoped; inject into services, not singletons
• 🟢 Use async LINQ (ToListAsync, SaveChangesAsync) on I/O paths
• 🟡 Use AsNoTracking() for read-only queries and API list endpoints
• 🟡 Review migration SQL before applying to production
• 🔴 Never use EnsureCreated() in shared or production databases
• 🔴 Log generated SQL in dev; monitor slow queries in production

Interview questions

Fresher level

Q1: Explain Transaction Optimization in an EF Core interview.
A: Define the concept, show a ShopNest entity/query example, mention tracking implications, and one production pitfall you avoided.

Q2: Code First vs Database First — when to use which?
A: Code First for greenfield; scaffold from existing DB for legacy; raw SQL/Dapper for hot reporting paths.

Q3: Explain the EF Core query pipeline.
A: LINQ → expression tree → SQL generator → database → data reader → materialization → optional change tracking.

Mid / senior level

Q4: How do you fix N+1 queries?
A: Use Include/projection, split queries, or explicit loading; verify with logged SQL and profiling.

Q5: DbContext lifetime in ASP.NET Core?
A: Register as Scoped — one context per request; never singleton with concurrent requests.

Q6: EF Core vs Dapper vs raw ADO.NET?
A: EF for productivity and change tracking; Dapper/ADO for hand-tuned reads and bulk operations.

Coding round

Write a LINQ query: top 3 customers by total order value on ShopNest orders.

var top = await _context.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new { CustomerId = g.Key, Total = g.Sum(o => o.GrandTotal) })
    .OrderByDescending(x => x.Total).Take(3).ToListAsync();

Summary & next steps

• Article 68: Transaction Optimization in EF Core
• Module: Module 7: Performance Optimization · Level: ADVANCED
• Applied to ShopNest.Data — Orders

Previous: Connection Pooling in EF Core
Next: Memory Optimization in EF Core

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

FAQ