Q06 proof · EF Core, Dapper and other data-access frameworks

6. ORM and data-access frameworks

Answer as submitted

“Yes — EF Core (code-first, migrations, LINQ) and Dapper, plus Hibernate/JPA and SQLAlchemy.”

Technical decision: expand. The submitted line is accurate but compressed — it names the frameworks without the reasoning that decides between them. This page is the separate, detailed version: it shows the call each one is used for, the SQL it produces, and the rules that keep persistence out of the domain.

Expanded answer

Yes. EF Core is my default for domain modelling and transactional writes — code-first entities, migrations under source control, LINQ translated to set-based SQL, Include with split queries for relationships, and explicit transactions committed through a Unit of Work. Dapper takes the result-set-shaped reads: reporting queries, multi-mapping over joins and stored-procedure paths where I want exact SQL with minimal mapping overhead. Behind both sits the same repository abstraction, which is also how I bring Hibernate/JPA on Java and SQLAlchemy on Python into the same shape.

Decision	Typical call	Why this tool
Domain writes	`DbContext` + Unit of Work	Change tracking and one transaction per business operation.
Relationship loading	`Include` + `AsSplitQuery`	Removes N+1 round trips without a cartesian explosion.
Hot read path	`AsNoTracking` + compiled query	No tracking allocation and a reused query plan.
Reporting read	`QueryAsync<T>` (Dapper)	Projects straight to a DTO with exact, parameterised SQL.
Schema versioning	`dotnet ef migrations add`	The schema evolves with the code and is reviewable.
Other runtimes	JPA/Hibernate, SQLAlchemy	The same repository boundary across .NET, Java and Python.

Live data-access workflow proof

The running component below changes its call/SQL view through real event binding. Select an operation to see the data-access call, the reasoning behind it, and the observable result — generated SQL, row counts and timings.

ORM WORKFLOWRUNNING

Choose a data-access operation

Select an operation to inspect the call, the reasoning and the SQL or result it produces.

$ db.Orders.Where(o => o.CustomerId == 42).OrderByDescending(o => o.CreatedAt).Take(20).ToListAsync()

EF Core translates the expression tree to set-based SQL, so filtering and paging happen in the database instead of in memory.

SELECT TOP(20) [o].[Id], [o].[CustomerId], [o].[CreatedAt], [o].[Total]
FROM [Orders] AS [o]
WHERE [o].[CustomerId] = @__customerId_0
ORDER BY [o].[CreatedAt] DESC
-- 4 ms  ·  20 rows

How the choice is made

Rule of thumb. EF Core for the majority of the domain and every transactional write, Dapper where the query is defined by its result set rather than by an entity graph, and hand-tuned SQL only when the optimiser needs explicit guidance. Whatever the tool, the query is reviewed against its execution plan before release, and persistence stays behind an interface so it can be swapped or mocked in tests.

Boundary for Q06: this page covers the ORM and micro-ORM choice and the data-access patterns around it. Provider-specific tuning, migration strategies for large production schemas and ORM-versus-stored-procedure benchmarking are separate topics.

Source files

Select a file to inspect the proof with syntax highlighting and line numbers. The first file is the code running the component above; the other two are the production shapes of the same decisions.

Acceptance criteria

Names EF Core and Dapper and the role each one is given.
Distinguishes tracked writes, no-tracking reads and result-set-shaped reads.
Shows an interactive proof of choosing a data-access operation and reading its result.
Demonstrates the N+1 fix and the Unit of Work commit path.
Defers provider tuning, large-schema migrations and benchmarking to a later topic.