EF Core with SQL Server — Advanced Features

Introduction

ShopNest Financial Reporting needs transactions, resilient connections, JSON columns, hierarchical accounts, and multi-tenant data isolation — SQL Server features EF Core 8 exposes cleanly.

After this article you will

• Wrap operations in explicit transactions
• Configure connection retry policies
• Map JSON columns and temporal tables
• Design multi-tenant filters
• Integrate full-text search where needed

Prerequisites

• Article 21 — Database First Approach with EF Core
• ShopNest DbContext with Products/Orders from Articles 13–15
• SQL Server or LocalDB configured

Concept deep-dive

// Explicit transaction
await using var tx = await _db.Database.BeginTransactionAsync();
try
{
    _db.LedgerEntries.Add(debit);
    _db.LedgerEntries.Add(credit);
    await _db.SaveChangesAsync();
    await tx.CommitAsync();
}
catch { await tx.RollbackAsync(); throw; }

// Connection resiliency (Program.cs)
options.UseSqlServer(connectionString, sql =>
    sql.EnableRetryOnFailure(maxRetryCount: 5));

// JSON column (.NET 8 + SQL Server)
public class ReportSnapshot
{
    public int Id { get; set; }
    public ReportMetadata Metadata { get; set; } // mapped to nvarchar(max) JSON
}

// Global query filter — multi-tenancy
modelBuilder.Entity<Order>()
    .HasQueryFilter(o => o.TenantId == _tenantProvider.TenantId);

Temporal tables: SQL Server system-versioned tables — EF Core 8 can map history. Hierarchy: adjacency list or closure table for chart of accounts.

Hands-on — ShopNest Financial Reporting System

1. Transfer funds service: debit/credit in one transaction.
2. Enable retry on failure for Azure SQL transient errors.
3. TenantId column + query filter on financial entities.
4. Store report config as JSON owned type.

Common errors & best practices

• Nested SaveChanges without transaction — partial updates on failure.
• Query filter forgotten in raw SQL — tenant data leak; always filter TenantId.
• No retry on cloud SQL — transient failures become user-visible 500s.

Interview questions

Q: When explicit transaction?
A: Multiple SaveChanges or cross-context operations must succeed or fail together.

Q: Multi-tenancy with EF?
A: Global query filter on TenantId + set TenantId on insert via interceptor.

Q: EnableRetryOnFailure?
A: Handles transient Azure SQL errors (throttling, failover) automatically.

Summary

• Transactions ensure atomic financial transfers
• Retry policies essential for cloud SQL Server
• JSON columns and query filters support modern reporting
• Temporal tables and hierarchy for audit and COA

Previous: Database First Approach with EF Core
Next: Dependency Injection in ASP.NET Core

FAQ