The SQL Query That Would Have Locked Our Entire Orders Table — Caught by Formatting

The setup

We were migrating a legacy batch process to EF Core. The original process ran a hand-written SQL script that updated order statuses in bulk — setting orders older than 90 days with status "pending" to "expired". We rewrote it as an EF Core ExecuteUpdateAsync call for cleaner code and type safety. The query went through code review, our CI pipeline ran the unit tests, and everything looked correct. We were ready to deploy to production and run the migration.

Before deploying, we decided to capture the actual SQL that EF Core would generate and review it one more time. We ran it against our staging database with SQL Server Profiler attached, captured the output, and pasted it into our notes. It was one long line. We were about to run it in production when someone suggested formatting it first to make the review more systematic.

What the captured query looked like — unformatted

When you capture EF Core queries with SQL Server Profiler or the EF Core logging interceptor, the output is compact and parameter-substituted. The query we captured from Profiler looked like this:

UPDATE [o] SET [o].[Status] = N'Expired', [o].[UpdatedAt] = '2026-07-07T08:23:41.2341200' FROM [Orders] AS [o] INNER JOIN [Customers] AS [c] ON [o].[CustomerId] = [c].[Id]

At a quick glance, that looked reasonable. There's a JOIN to the Customers table, and we're setting Status and UpdatedAt. Nothing obviously wrong. The query is short enough to read in a few seconds. We almost approved it right there.

What formatting revealed

UPDATE [o]
SET
    [o].[Status] = N'Expired',
    [o].[UpdatedAt] = '2026-07-07T08:23:41.2341200'
FROM [Orders] AS [o]
INNER JOIN [Customers] AS [c] ON [o].[CustomerId] = [c].[Id]

No WHERE clause. This query would update every row in the Orders table to status "Expired", regardless of the current status or how old the order was. All 2.7 million orders. The condition we had written in C# had been silently dropped during the EF Core query generation due to a bug in how we had written the predicate.

What the buggy C# code looked like

// Buggy version — the Where clause was accidentally on the wrong subquery
public async Task ExpireOldOrdersAsync()
{
    var cutoffDate = DateTime.UtcNow.AddDays(-90);

    await _context.Customers
        .Where(c => c.IsActive)       // filter is on Customers, not Orders
        .SelectMany(c => c.Orders)     // gets all orders for active customers
        .ExecuteUpdateAsync(setters => setters
            .SetProperty(o => o.Status, "Expired")
            .SetProperty(o => o.UpdatedAt, DateTime.UtcNow));
    // Missing: no date filter on the orders themselves
}

The intended query had two conditions: orders where status is "pending" AND where the order date is older than 90 days. The Where we had written filtered on customers (active customers only), not on orders. The order date filter had been dropped entirely. We had assumed the SelectMany with a customer filter would constrain the order update, but it only constrained which customers' orders were considered — it didn't add any condition about the orders' own age or status.

The correct code

// Fixed version — explicit predicate on the Orders table
public async Task ExpireOldOrdersAsync()
{
    var cutoffDate = DateTime.UtcNow.AddDays(-90);

    int affected = await _context.Orders
        .Where(o => o.Status == "Pending"         // only pending orders
                 && o.CreatedAt < cutoffDate       // older than 90 days
                 && o.Customer.IsActive)            // belonging to active customers
        .ExecuteUpdateAsync(setters => setters
            .SetProperty(o => o.Status, "Expired")
            .SetProperty(o => o.UpdatedAt, DateTime.UtcNow));

    _logger.LogInformation("Expired {Count} orders older than {CutoffDate}",
        affected, cutoffDate.ToString("yyyy-MM-dd"));
}

The generated SQL from this version includes a proper WHERE clause with all three conditions. The formatted output is unambiguous:

UPDATE [o]
SET
    [o].[Status] = N'Expired',
    [o].[UpdatedAt] = '2026-07-07T08:23:41.2341200'
FROM [Orders] AS [o]
INNER JOIN [Customers] AS [c] ON [o].[CustomerId] = [c].[Id]
WHERE
    [o].[Status] = N'Pending'
    AND [o].[CreatedAt] < '2026-04-07T08:23:41.2341200'
    AND [c].[IsActive] = CAST(1 AS bit)

How to capture and review EF Core SQL in .NET

For development and pre-deployment review, there are several ways to see the SQL EF Core generates:

// Option 1: Log all SQL via ILoggerFactory (useful in development)
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .LogTo(Console.WriteLine, LogLevel.Information)
           .EnableSensitiveDataLogging()); // includes parameter values

// Option 2: Interceptor to capture specific queries (useful in integration tests)
public class SqlCaptureInterceptor : DbCommandInterceptor
{
    public List<string> CapturedSql { get; } = new();

    public override DbCommand CommandCreated(
        CommandEndEventData eventData, DbCommand result)
    {
        CapturedSql.Add(result.CommandText);
        return result;
    }
}

// Option 3: ToQueryString() for read queries (does not work for ExecuteUpdate/Delete)
var queryString = _context.Orders
    .Where(o => o.Status == "Pending")
    .ToQueryString();
Console.WriteLine(queryString); // prints the parameterized SQL

For ExecuteUpdateAsync and ExecuteDeleteAsync, SQL Server Profiler or the LogTo interceptor are the most reliable ways to capture the generated query before running it. Neither ToQueryString() nor manual ToString() on the IQueryable works for these bulk operation methods.

The process change we made

After this near-miss, we added a mandatory step to our deployment checklist for any migration or batch script that modifies data: capture the SQL from a dry run against a staging database, format it, and get explicit sign-off that the WHERE clause matches the intended scope. It takes two minutes. The alternative, as we almost discovered firsthand, is a full-table update on a 2.7-million-row production table at 8 AM on a Monday.

We also added a test that counts the expected number of affected rows against a seeded dataset before running any bulk update in the deployment pipeline. If the affected count is significantly higher than expected, the job aborts and pages the on-call engineer. This is not a substitute for reading the SQL, but it's a second line of defense.

The generalizable lesson

Minified, single-line SQL is as unreadable as minified JSON or minified JavaScript. The structure — the WHERE clause, the JOIN conditions, the RETURNING clause — is invisible without formatting. Every ExecuteUpdate, ExecuteDelete, raw SQL, or stored procedure call that touches more than a handful of rows should be formatted and reviewed as SQL before it runs on production data. The formatting step is reversible. An unbounded UPDATE is not.

SQL Formatter Saved Me From Deploying a Query That Would Have Locked Our Entire Orders Table