SQL tips and tricks

Two users read the same record:

• Both modify it
• Both save it

Who wins?

Without concurrency protection, the second write might silently overwrite the first.

One common solution is optimistic concurrency.

For example, keep a version number with the record:

Order
Id: 42
Status: Pending
Version: 7

Update it only if the version is still 7:

UPDATE Orders
SET Status = 'Completed',
    Version = 8
WHERE Id = 42
  AND Version = 7;

If zero rows are updated, somebody changed the record after you read it.

Your application can then reject, retry, merge, or ask the user to resolve the conflict.

If multiple actors can update the same data, checking whether it changed before overwriting it is often useful.

ORM query behaving differently than expected?

Look at the generated SQL.

In EF Core, you can inspect a query with:

var query = context.Orders
    .Where(x => x.Status == OrderStatus.Completed)
    .OrderByDescending(x => x.CreatedAt);

var sql = query.ToQueryString();

Generated SQL helps you see:

• Which columns are selected
• Which joins were generated
• Where filters are applied
• How ordering works
• Whether the query matches your assumptions

LINQ is convenient, but the database still executes SQL.

When a database query is slow or surprising, looking at the database query is often useful.

Need to update many rows in EF Core?

You don't always need to load them first.

Instead of:

var users = await context.Users
    .Where(x => !x.IsActive)
    .ToListAsync();

foreach (var user in users)
{
    user.Status = UserStatus.Disabled;
}

await context.SaveChangesAsync();

You can update matching rows directly:

await context.Users
    .Where(x => !x.IsActive)
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(x => x.Status, UserStatus.Disabled));

ExecuteUpdateAsync() is useful when:

• You need to update many rows
• You don't need the entities in memory
• You don't need change tracking
• The update can be expressed as a database operation
• You want to avoid unnecessary round trips and allocations

The update runs directly in the database.

If you don't need the entities, loading them before updating them is optional.

Need only active customers?

Filter them in the database.

Instead of:

var customers = await context.Customers.ToListAsync();

var activeCustomers = customers
    .Where(x => x.IsActive)
    .ToList();

Prefer:

var activeCustomers = await context.Customers
    .Where(x => x.IsActive)
    .ToListAsync();

The difference is where filtering happens.

The first approach:

• Loads every row
• Transfers every row
• Stores every row in memory
• Filters afterward

The second approach lets the database return the rows you requested.

If you need 100 customers from a table containing 1,000,000 rows, filtering before ToListAsync() tends to matter.

Reading data that you don't plan to update?

Consider AsNoTracking().

var products = await context.Products
    .AsNoTracking()
    .Where(x => x.IsActive)
    .ToListAsync();

By default, EF Core tracks entities so it can detect changes and persist them later.

For read-only scenarios, that tracking work might not be needed.

AsNoTracking() helps by:

• Avoiding change tracking
• Reducing tracking-related memory usage
• Making the intent of the query explicit
• Working well for API reads and reporting
• Keeping read-only queries focused on reading

If you don't plan to change the entity, consider asking EF Core not to track changes.

Need your SQL results in a specific order?

Use ORDER BY.

Common options:

• ASC sorts values in ascending order
• DESC sorts values in descending order
• Sort by one column
• Sort by multiple columns
• Combine sorting with WHERE

Without ORDER BY, the database does not guarantee the order of returned rows.

If order matters, ask the database to order the results.

Need to remove data from a database?

Use DELETE.

Common examples:

• DELETE removes rows
• WHERE specifies which rows
• No WHERE means all rows
• Transactions help protect changes
• ROLLBACK helps when something goes wrong

Before executing a DELETE, make sure your WHERE condition selects the rows you want to remove.

Need to retrieve data from a database?

A typical SQL query consists of these clauses:

• SELECT to choose columns
• FROM to specify the table
• JOIN to combine related tables
• WHERE to filter rows
• GROUP BY to group results
• HAVING to filter groups
• ORDER BY to sort data
• OFFSET / FETCH or LIMIT to paginate

Not every query needs every clause, but this is the structure you'll see in most SQL statements.

Understanding what each clause does makes reading and writing SQL much easier.

Queries running slowly?

Consider adding an index.

Indexes help SQL Server:

• Find data faster
• Reduce table scans
• Improve filtering performance
• Improve sorting performance
• Improve join performance

Not every column needs an index, but columns used in WHERE clauses often benefit from one.

What is your favorite indexing strategy?

Want asynchronous database writes in EF Core?

Use SaveChangesAsync() instead of SaveChanges().

Benefits:

• Doesn't block the calling thread
• Works better under load
• Integrates with async/await
• Recommended for ASP.NET Core applications
• Available since EF Core was released

What other EF Core performance tips do you use?