Database

Your Customer entity has 47 properties. Most queries only need the basic information: name, email, and contact details. But every time you load a customer, EF Core pulls all 47 columns from the database, including the large ProfileData JSON blob that’s rarely used.

This is where table splitting can help: split your large entity into multiple classes that map to the same table, loading only what you need when you need it.

But table splitting is a double-edged sword. Use it wrong, and you’ll make performance worse, not better.

Understanding Table Splitting

Table splitting maps multiple entity classes to the same database table:

// Single table with many columns
CREATE TABLE Customers (
    Id int PRIMARY KEY,
    Name nvarchar(100),
    Email nvarchar(200),
    Phone nvarchar(20),
    -- Frequently accessed columns above
    
    ProfileData nvarchar(max),
    Preferences nvarchar(max), 
    MarketingMetadata nvarchar(max),
    LastLoginDetails nvarchar(max),
    -- Rarely accessed columns below
    CreatedAt datetime2,
    ModifiedAt datetime2
);

Instead of one large entity, you create multiple focused entities:

// Frequently accessed data
public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    
    // Navigation to extended data
    public CustomerExtended Extended { get; set; }
}

// Infrequently accessed data
public class CustomerExtended
{ …

Your EF Core LINQ query for monthly sales reporting takes 45 seconds to execute. You rewrite it as a stored procedure and it runs in 3 seconds. But now your business logic is split between C# and SQL, and your team is wondering if they made the right choice.

The decision between EF Core LINQ and stored procedures isn’t black and white. Each approach has strengths that match different scenarios.

When EF Core LINQ Excels

EF Core handles most database operations beautifully with clean, maintainable code:

// Clean, readable, and performs well
var customerOrders = await context.Customers
    .Where(c => c.Country == "USA")
    .Include(c => c.Orders.Where(o => o.OrderDate >= DateTime.Now.AddMonths(-3)))
    .Select(c => new CustomerOrderSummary
    {
        CustomerId = c.Id,
        CustomerName = c.Name,
        RecentOrderCount = c.Orders.Count(),
        TotalAmount = c.Orders.Sum(o => o.Total)
    })
    .ToListAsync();

EF Core generates efficient SQL for this type of query:

SELECT [c].[Id], [c].[Name], 
       COUNT([o].[Id]) AS RecentOrderCount,
       COALESCE(SUM([o].[Total]), 0.0) AS TotalAmount
FROM [Customers] AS [c]
LEFT JOIN [Orders] AS [o] ON [c].[Id] = [o].[CustomerId] 
    AND [o].[OrderDate] >= @__AddMonths_0
WHERE [c].[Country] = N'USA'
GROUP BY [c].[Id], [c].[Name]

This performs well and keeps all logic in your application layer.

When to Consider Stored Procedures

Complex Business Logic with Multiple Steps

Some …

Your e-commerce site processes millions in revenue daily. A simple database migration that takes your site offline for 10 minutes costs thousands in lost sales and damages customer trust.

Traditional migration approaches require downtime: stop the application, run migrations, restart with new code. Zero-downtime migrations eliminate this disruption using careful planning and deployment strategies that keep your application running throughout database updates.

The Zero-Downtime Challenge

Standard EF Core migrations assume a simple deployment model:

1. Stop application
2. Run dotnet ef database update
3. Deploy new application code
4. Start application

This works for small applications but creates unacceptable downtime for production systems. The challenge is that database schema changes often require corresponding application code changes, creating a chicken-and-egg problem.

Backward-Compatible Migration Patterns

Zero-downtime migrations require backward compatibility: the database must work with both old and new application versions during the transition.

Adding New Tables

Adding new tables is always safe:

public partial class AddOrderHistoryTable : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "OrderHistory",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", …

Your team of 8 developers is working on different features. Developer A adds a new table, Developer B modifies an existing column, Developer C renames a property. All three create migrations on the same day.

When you try to merge everything together, EF Core explodes with migration conflicts, database schema mismatches, and mysterious errors about missing tables. Sound familiar?

Large teams need structured approaches to EF Core migrations, or you’ll spend more time fixing database issues than building features.

The Migration Conflict Problem

EF Core migrations are sequential by design. Each migration has a timestamp-based filename and builds on the previous one:

20250923_081234_AddCustomerTable.cs
20250923_091456_AddOrderTable.cs      // Depends on Customer table
20250923_095612_AddCustomerEmail.cs   // Also modifies Customer table

When multiple developers create migrations simultaneously, you get parallel branches that can’t merge cleanly:

Feature Branch A: AddCustomerTable -> AddCustomerEmail
Feature Branch B: AddCustomerTable -> AddOrderTable
Feature Branch C: AddCustomerTable -> ModifyCustomerName

Merging these creates a broken migration history where later migrations reference schema changes that don’t exist in the merged timeline.

Team Workflow Strategy

1. Feature Branch Guidelines

Each developer works in feature branches with clear migration rules:

# Developer starts new feature
git checkout -b feature/customer-management
dotnet ef migrations …

Two users edit the same customer record simultaneously. User A updates the phone number, User B changes the address. User A saves first, then User B saves. Result: the phone number update disappears.

This is the classic lost update problem, and it happens more often than you think in production web applications. EF Core concurrency tokens solve this elegantly without the complexity of database locks.

The Lost Update Problem

Without concurrency control, this scenario plays out daily in production:

// User A loads customer
var customer = await context.Customers.FindAsync(customerId);
customer.Phone = "555-0123";

// User B loads the same customer (before A saves)
var customer2 = await context.Customers.FindAsync(customerId);
customer2.Address = "123 New Street";

// User A saves first
await context.SaveChangesAsync(); // Phone updated

// User B saves second  
await context.SaveChangesAsync(); // Address updated, but phone reverted!

User B’s save overwrote User A’s phone number change because EF Core generated an UPDATE statement based on the original values User B loaded. The phone number silently reverted to its original value.

Concurrency Tokens to the Rescue

Concurrency tokens enable optimistic concurrency control. EF Core includes the token value in UPDATE and DELETE statements, ensuring the operation only succeeds if no one else modified the record:

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } …

Your application works perfectly in development, but in production, you’re seeing partial data updates and mysterious consistency issues. The problem isn’t your business logic, it’s how you’re handling transactions.

EF Core gives you two ways to manage transactions: the automatic approach with SaveChanges, and explicit transaction control. Each has its place, but using the wrong one can cost you data integrity or performance.

How EF Core SaveChanges Handles Transactions

SaveChanges automatically wraps all your changes in a single database transaction. This means multiple entity operations either all succeed or all fail together:

// This entire operation is one transaction
using var context = new OrderContext();

var customer = new Customer { Name = "John Doe" };
var order = new Order { CustomerId = customer.Id, Total = 100.50m };
var orderItem = new OrderItem { OrderId = order.Id, ProductId = 1 };

context.Customers.Add(customer);
context.Orders.Add(order);
context.OrderItems.Add(orderItem);

await context.SaveChangesAsync(); // All or nothing

If any part fails, everything rolls back. Your database stays consistent without any extra code.

When SaveChanges Transactions Aren’t Enough

Multiple SaveChanges Calls

The automatic transaction only covers a single SaveChanges call. If your business logic requires multiple save operations, you need explicit control:

// Problem: Two separate transactions
public async Task ProcessOrderAsync(Order …

In one of our enterprise SaaS projects last year, we discovered a critical bug during a routine audit. Customer A could see order data belonging to Customer B when filtering by a specific date range. The root cause? A missing WHERE TenantId = @tenantId clause in a complex reporting query.

This wasn’t just embarrassing. It was a potential GDPR violation that could have resulted in significant penalties. That incident taught us that manual tenant filtering is prone to human error, especially in large codebases with multiple developers.

EF Core’s global query filters solved this problem by automatically applying tenant isolation at the ORM level. Here’s how we implemented bulletproof tenant isolation that passes SOC2 and HIPAA compliance requirements.

The Multi-Tenant Entity Foundation

Every entity in our system includes a TenantId property. This isn’t optional, it’s the foundation of data isolation.

public class Order
{
    public int Id { get; set; }
    public Guid TenantId { get; set; }
    public string CustomerName { get; set; }
    public decimal Amount { get; set; }
    public DateTime CreatedAt { get; set; }
}

public class Product
{
    public int Id { get; set; }
    public Guid TenantId { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

The TenantId is resolved through dependency injection using a tenant provider service:

public interface ITenantProvider
{
    Guid TenantId { get; }
}

public …