Suggestions about Database

Adding an idempotency key to an API doesn't automatically make the operation idempotent.

The key needs to identify the operation you want to execute once.

Consider payment creation:

POST /payments
Idempotency-Key: order-123-payment

The client times out and retries:

POST /payments
Idempotency-Key: order-123-payment

The server recognizes the same logical operation and returns the previous result instead of creating another payment.

Now imagine generating a new key for every retry:

Request 1 → 7f9a...
Request 2 → 82bc...
Request 3 → a14e...

From the server's perspective, those are three different operations.

A good idempotency key should match the scope of the business operation.

Think about:

• What exactly should happen once?
• How long should the key remain valid?
• Which caller owns the key?
• Should the same key with different payloads be rejected?
• Where is the result of the original operation stored?

Idempotency isn't about making requests unique.

It is about recognizing when multiple requests represent the same operation.

If every retry gets a new identity, the server has no reason to know it's a retry.

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.

Application feels slow?

Before optimizing it, find out what is slow.

Use profiling, tracing, metrics, or benchmarks to measure where the application spends its time.

You might discover that a request spends:

Validation        4 ms
Business logic   12 ms
Database        680 ms
Serialization     8 ms

Optimizing 12 ms of business logic probably won't fix a 700 ms request.

Measure things like:

• Execution time
• Database calls
• External requests
• CPU usage
• Memory allocations
• Lock contention

Then optimize the part responsible for the problem.

Performance optimization becomes much easier once you know what needs to become faster.

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.