Reads Are Part of the Contract: A DBA’s Guide to ORM-Generated SQL
In the first post in this series I argued that the database is a published interface, and that the way to keep it changeable is to draw a boundary: a small, deliberate set of views and procedures is the contract, and the physical tables behind them are private implementation you are free to reshape.
There is a large hole in that argument, and it is shaped exactly like an ORM.
When an application uses Entity Framework, Hibernate, ActiveRecord, or any other object-relational mapper in the usual way, it maps its objects directly onto physical tables and reads from them directly. There is no view in the middle. The moment that happens, every column the ORM touches becomes part of the published contract, whether you meant it to or not. The boundary you carefully drew on the write side quietly disappears on the read side. This post is about getting it back without doing the thing everyone expects a DBA to want, which is to rip the ORM out. I do not want you to rip the ORM out. I want you to know what it is promising on your behalf.
Reading Is Coupling
It is tempting to think of reads as harmless. A SELECT does not change data, so how much trouble can it cause? The trouble is not what the query does to the data; it is what the query assumes about the schema. A read couples the consumer to everything it names and a few things it does not.
Consider the most ordinary query an ORM can emit:
|
1 2 3 4 5 6 7 8 9 |
SELECT [c].[customer_id] , [c].[legal_name] , [c].[email] , [c].[created_utc] FROM [dbo].[customer] AS [c] WHERE [c].[customer_id] = @p0; |
That query is now coupled to four column names, their data types, the table name, and the schema. Rename legal_name, split it into two columns, change email from nvarchar to a child table, or move customer into a different schema, and this query breaks. Multiply it by the hundreds of mapped entities in a real application and you have a contract with hundreds of clauses, none of which anyone wrote down, all of which the ORM is enforcing silently every time it builds a query from your class definitions.
This is the same published-versus-public distinction from the first post, viewed from the read side. The application’s object model has been wired straight onto the implementation. Martin Fowler’s complaint about the anemic domain model – objects that look like a domain but carry no behavior, just data mapped from tables – is the application-side symptom of the same coupling.[1] The classes mirror the tables because the ORM made mirroring the tables the path of least resistance.
You Cannot Fix What You Cannot See
Before you can decide what the read contract should be, you have to see what the ORM is actually sending, because it is rarely what the developer thinks they wrote. A three-line LINQ expression can expand into a query with a dozen joins, a correlated subquery per row, or a SELECT that drags back forty columns to populate a screen that shows three. There are three good places to look, and you should use all of them.
Turn on the ORM’s own logging in development. Entity Framework Core will write every statement it executes to any logger you give it, including the raw SQL, with a single call to LogTo.[2] This is the fastest feedback loop, because the developer sees the generated SQL next to the code that produced it, in their own console, before anything ships. Most ORM surprises die quietly here once people simply look.
Use Query Store on the server. Query Store captures the queries that actually ran, their plans, and their runtime statistics, and it keeps that history across restarts so you can see when a plan changed and what it cost.[3] It is the single best tool for finding the ORM query that was fine in testing and fell over under production data volumes, because it shows you the regression and the plan that caused it.
Aggregate by query in the DMVs. When you want a quick ranking of the heaviest statements without the Query Store UI, sys.dm_exec_query_stats exposes per-plan execution counts and cumulative CPU, reads, and duration that you can sort to find the worst offenders.[4]:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SELECT TOP (25) [qs].[execution_count] , [qs].[total_logical_reads] , [qs].[total_logical_reads] / [qs].[execution_count] AS [avg_reads] , [qs].[total_worker_time] / [qs].[execution_count] AS [avg_cpu_us] , SUBSTRING ( [st].[text] , ([qs].[statement_start_offset] / 2) + 1 , ( ( CASE [qs].[statement_end_offset] WHEN -1 THEN DATALENGTH([st].[text]) ELSE [qs].[statement_end_offset] END - [qs].[statement_start_offset] ) / 2 ) + 1 ) AS [statement_text] FROM [sys].[dm_exec_query_stats] AS [qs] CROSS APPLY [sys].[dm_exec_sql_text]([qs].[sql_handle]) AS [st] ORDER BY [avg_reads] DESC; |
The ORM queries stand out immediately: they are the ones with the machine-generated parameter names @__p_0, the wide column lists, and the execution counts in the hundreds of thousands.
The Pathologies, and What Each One Couples You To
Once you can see the SQL, the same handful of patterns show up over and over. Each one is worth naming, because each one expands the contract in a specific way.
Select everything. An ORM that materializes whole entities tends to select every mapped column, every time, even for a screen that needs three of them. That couples the consumer to columns it never uses, so adding or widening an unrelated column changes the payload and the plan for queries that had no business caring. The fix is projection: have the ORM select into a small read model (a DTO) that names only the columns the caller needs. Entity Framework’s own performance guidance leads with exactly this point.[5]
The N+1 query. Loading a list of orders and then lazily loading each order’s customer one row at a time turns one screen into one query plus N more. It is invisible in the code, which reads like a simple loop, and obvious in the log, which shows the same parameterized query firing hundreds of times. This is the pathology ORM logging was made to catch, and the fix is to load the related data in one query rather than per row.
The unparameterized IN list. Some ORMs expand a list filter into IN (@p0, @p1, @p2, ...) with a different number of parameters every call, so each distinct list length compiles a separate plan and bloats the plan cache. This is one of the few cases where the cleanest fix reaches back into the contract: expose a table-valued parameter or a stored procedure that takes a TVP, and let the application pass a set instead of a hand-assembled list.
Implicit conversions. When the ORM declares a parameter as nvarchar and the column is varchar (or vice versa), SQL Server may convert the column rather than the parameter, which can disqualify an index seek and quietly turn a fast lookup into a scan. This one is invisible until you read the plan, and it is a type mismatch in the contract between what the application thinks the column is and what it actually is.
None of these require removing the ORM. Every one of them is fixed either in how the application uses the ORM or in what surface the database hands it.
Give the ORM a Stable Read Surface
The structural fix, the one that restores the boundary from the first post, is to stop letting the ORM map directly onto the physical tables and instead map it onto a read surface you control. In SQL Server that surface is a set of views, and optionally stored procedures, that the ORM treats as if they were tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
/* A read view the ORM maps to instead of dbo.customer directly. It names a stable set of columns and hides the physical layout. */ CREATE OR ALTER VIEW [api].[customer_read] AS SELECT [customer_id] , [legal_name] AS [customer_name] , [email] AS [email_address] , [created_utc] FROM [dbo].[customer]; GO |
Map the entity to [api].[customer_read] and the application is now coupled to the view, not the table. When the physical customer table is refactored, the view absorbs the change and the ORM never notices, which is the whole point of the boundary. This is not free: a view can hide an expensive join behind a friendly name, and a deep stack of views over views is its own kind of debt. Use it deliberately, for the entities that genuinely have outside or long-lived consumers, not reflexively for every table.
For the heaviest, most repeated read shapes, you can go one step further and give the ORM a stored procedure or an indexed view, trading a little mapping convenience for a plan you fully control. The trade is the same one the contract boundary always asks for: a named, stable interface in exchange for the freedom to change what is behind it. It is also the read-side version of the argument I made for keeping significant logic close to the data in the business-logic series – the ORM is wonderful for the ordinary 90 percent, and the database earns its keep on the other 10.
The Posture to Take
The anti-ORM DBA and the ORM-everything developer are having the wrong argument. The ORM is not the problem and it is not the solution; it is a tool that, left to its defaults, will couple your application to the physical shape of your tables and call it convenience. Your job is not to forbid it. Your job is to know what it is reading, to catch the handful of pathologies that actually cost you, and to hand it a read surface stable enough that the tables underneath stay yours to change.
Do that and reads stop being a hole in the contract and become part of it, on purpose.
Next in the series, we put the whole contract – schema, views, procedures, and the migrations that change them – under the same build pipeline as the application code, because a contract you cannot deploy reliably is not much of a contract. That is Putting the Contract Under CI/CD.
How does your team keep its ORM honest? I would like to hear what has worked. Find me on Bluesky or LinkedIn.
References and Footnotes
- AnemicDomainModel – Martin Fowler. The anti-pattern of domain objects that carry data mapped from tables but little behavior, a common application-side symptom of mapping objects straight onto the physical schema. ↩
- Simple logging – EF Core, Microsoft Learn. How to make Entity Framework Core emit the raw SQL it executes via
LogTo, the fastest way to see generated SQL next to the code that produced it. ↩ - Monitor performance by using the Query Store – Microsoft Learn. Captures executed queries, their plans, and runtime statistics with history across restarts, the primary tool for finding ORM queries that regress under production volumes. ↩
- sys.dm_exec_query_stats (Transact-SQL) – Microsoft Learn. Per-plan execution counts and cumulative CPU, reads, and duration for cached queries, used here to rank the heaviest statements. ↩
- Efficient Querying – EF Core, Microsoft Learn. Performance guidance that leads with projecting only the columns you need and avoiding loading whole entities and related data inadvertently. ↩