Data access in .NET when performance, coherence, and maintenance all matter

The data layer is not a detail: it is where control is lost

Most performance and maintainability problems in the .NET systems I analyze originate in the data layer.

Not in the domain model, not in the application architecture: in the way the database is accessed.

N+1 queries generated without noticing by Entity Framework.

Loading entire objects when only three fields are needed.

Transactions left open for the entire duration of an HTTP request.

Missing indexes on columns used in every WHERE clause.

Mappings between tables and objects that force unnatural query structures.

These are not rare errors.

They are recurring patterns in almost every codebase that grows without attention to the data layer.

The problem is not Entity Framework: it is using it without understanding what it generates.

This category exists to close this gap: not tutorials on how to configure the DbContext, but reasoning on how to design data access so the system stays fast, consistent, and modifiable as requirements change.

Entity Framework Core: when to use it well and when to pair it with Dapper

Entity Framework Core is the right tool for most data access scenarios in .NET.

It handles mapping, change tracking, migrations, and abstraction from the specific SQL dialect.

For CRUD operations, moderate queries, and schema maintenance, it is the most productive choice.

Problems emerge when EF Core is used for everything without measuring.

Automatically generated queries are not always optimal.

The change tracker has a cost.

Eager loading with Include can generate heavy joins where a separate query would be more efficient.

Dapper enters the picture for complex queries where total control over SQL is needed: reports with complex aggregations, queries on materialized views, bulk operations, legacy stored procedures.

It does not replace EF Core: it complements it in specific cases where SQL control matters more than the productivity of automatic mapping.

The pattern I use most often is EF Core for writes and transactional operations, Dapper or direct SQL queries for complex reads and reports.

This is not a sophisticated architecture: it is applied pragmatism.

Efficient queries in EF Core: the pitfalls to know

The N+1 problem is the most common trap with EF Core: for each element in a list an additional query is executed instead of a join.

It manifests when you iterate over a collection and access navigation properties that were not loaded, and can turn a list of a hundred elements into a hundred-and-one database queries.

The solution is explicit loading with Include() for the necessary navigation properties, or projection with Select() to load only the required fields without traversing the entire object.

Projection is often the best choice for read queries: it generates simpler SQL, loads less data, and does not activate the change tracker.

AsNoTracking() is another fundamental tool for read-only queries: it disables change tracking and reduces memory allocation.

On queries returning hundreds of objects, the difference is measurable.

The tool for diagnosing problems is EnableDetailedErrors() and the SQL query logging generated by EF Core.

In development, seeing the generated SQL is indispensable.

In production, Application Insights or SQL Server's query store reveal slow queries before they become incidents.

Migrations and schema evolution: how not to fear the database

EF Core migrations are one of the most underrated tools in the ecosystem.

They allow versioning the database schema together with the code, applying changes in a repeatable way, and rolling back when necessary.

The pitfalls of migrations almost always come from migrations that are too large or destructive applied without a plan.

Renaming a column in a single migration generates a DROP and an ADD, not a RENAME: all data in that column is lost if the transformation is not explicitly handled.

Migrations that modify existing data must be tested on a copy of the production database before being applied.

The pattern that reduces risk is a two-phase migration for breaking changes: first add the new structure while keeping the old one, migrate the code to use the new structure, then in a subsequent release remove the old one.

Slower, much safer.

In production, migrations should never run automatically at application startup.

They must be SQL scripts that are reviewed, applied in a controlled manner, and recorded as part of the deployment process.