Entity Framework & SQL Server: The Price of Abstraction

This page follows the path from the LINQ expression to the load on the server. It shows where to measure what, which common assumptions are wrong, and how a read-only SQL query shows you what your application actually executes. It is not against Entity Framework: many EF applications run well, and hand-written SQL is not automatically better. The point is to look at both sides together.

How large the difference can be is shown by an experiment from our lab: the same LINQ line with Contains() cost SQL Server about 0.9 seconds of CPU for the same list under EF Core 7, about 2 seconds under EF Core 8, and under EF Core 10 one or the other depending on the order of the calls. The C# code was the same in all cases.

From C# code to load on the server

Between one line of LINQ and the load on the server lie several stations. A decision is made at each, and each has its own place to measure.

Two consequences matter in everyday work. First: an optimization can take effect at one station and change nothing at another. Less work in EF does not mean less work in SQL Server, and vice versa. Second: the same C# code can produce different SQL, different plans, and a different load after an EF Core update, after a change of the compatibility level, or with different data.

The deep dives

Which observation matches yours?

Query: What does the application send to SQL Server?

The query groups statements of the same shape in Query Store and shows how many different texts and plans are behind them, how often they ran, and what they cost. Run it in the application's database. It is read-only; from SQL Server 2022, Query Store is enabled by default for new databases. Without Query Store, the second query below provides a similar view from the plan cache, but only for what is still in the cache since the last restart.

Query: Statements in Query Store

/* PSG · Entity Framework & SQL Server · Query 1
   What does the application really send to SQL Server? (Query Store)

   Tested in the PSG lab on 2026-11-03 on SQL Server 2017 CU31, 2019 CU32, 2022 CU27, and 2025 CU9
   (test database with Query Store enabled; statements in EF Core format, see lab report).

   - Read-only. Changes nothing.
   - Run it in the application database; Query Store must be enabled there
     (default for new databases from SQL Server 2022).
   - Permissions: VIEW DATABASE STATE (from SQL Server 2022, VIEW DATABASE PERFORMANCE STATE is enough).
     Without this permission, the query fails with error 262 (followed by 297).
   - Groups statements of the same shape (query_hash). Many texts for the same shape
     indicate values in the SQL text, e.g., IN lists with constants.
   - The column "marker" names suspected patterns, not proof that EF generated the statement.
   - Query Store stores totals per plan and interval, no individual executions and no parameter values.
   - Query Store stores the statement text without leading comments. A tag set with TagWith
     is therefore not in the text of this query; it is in the plan cache text (Query 2).
*/
DECLARE @hours int = 24;   -- period to look at

WITH runtime AS (
    SELECT p.query_id,
           SUM(rs.count_executions)                               AS executions,
           SUM(rs.count_executions * rs.avg_cpu_time)      / 1000.0 AS cpu_ms,
           SUM(rs.count_executions * rs.avg_duration)      / 1000.0 AS duration_ms,
           SUM(rs.count_executions * rs.avg_logical_io_reads)       AS logical_reads
    FROM sys.query_store_runtime_stats AS rs
    JOIN sys.query_store_runtime_stats_interval AS i
      ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
    JOIN sys.query_store_plan AS p
      ON p.plan_id = rs.plan_id
    WHERE i.end_time >= DATEADD(HOUR, -@hours, SYSDATETIMEOFFSET())
    GROUP BY p.query_id
), per_statement AS (
    SELECT q.query_hash,
           q.query_id,
           qt.query_sql_text,
           l.executions, l.cpu_ms, l.duration_ms, l.logical_reads,
           (SELECT COUNT(*) FROM sys.query_store_plan AS p2 WHERE p2.query_id = q.query_id) AS plans
    FROM sys.query_store_query AS q
    JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
    JOIN runtime AS l ON l.query_id = q.query_id
    WHERE q.object_id = 0          -- only statements outside procedures, functions, and triggers
      AND qt.query_sql_text NOT LIKE N'%sys.query_store[_]%'   -- without queries on Query Store views (including this one)
)
SELECT TOP (25)
    CONVERT(varchar(20), a.query_hash, 1)             AS query_hash,
    COUNT(*)                                          AS texts,
    SUM(a.plans)                                      AS plans,
    SUM(a.executions)                                 AS executions,
    CAST(SUM(a.cpu_ms)   AS decimal(18, 1))           AS cpu_ms_total,
    CAST(SUM(a.duration_ms) AS decimal(18, 1))        AS duration_ms_total,
    CAST(SUM(a.logical_reads) AS bigint)              AS logical_reads_total,
    CAST(SUM(a.cpu_ms) / NULLIF(SUM(a.executions), 0) AS decimal(18, 3)) AS cpu_ms_per_execution,
    CASE
        WHEN MAX(CASE WHEN a.query_sql_text LIKE '%OPENJSON(@%'          THEN 1 ELSE 0 END) = 1
             THEN 'List as JSON parameter'
        WHEN COUNT(*) > 1
             AND MAX(CASE WHEN a.query_sql_text LIKE '% IN ([0-9-]%'
                            OR a.query_sql_text LIKE '% IN (N''%'
                            OR a.query_sql_text LIKE '% IN (''%' THEN 1 ELSE 0 END) = 1
             THEN 'IN list, many texts'
        WHEN MAX(CASE WHEN a.query_sql_text LIKE '% IN (@%' THEN 1 ELSE 0 END) = 1
             THEN 'IN list with parameters'
        ELSE ''
    END                                               AS marker,
    LEFT(MIN(a.query_sql_text), 300)                  AS sample_text
FROM per_statement AS a
GROUP BY a.query_hash
ORDER BY SUM(a.cpu_ms) DESC
OPTION (MAXDOP 1);

Query: Statements in the plan cache

Without Query Store, and to see the TagWith tag:

/* PSG · Entity Framework & SQL Server · Query 2
   Substitute without Query Store: the same view from the plan cache.

   Tested in the PSG lab on 2026-11-03 on SQL Server 2017 CU31, 2019 CU32, 2022 CU27, and 2025 CU9.

   - Read-only. Permissions: VIEW SERVER STATE (from SQL Server 2022, VIEW SERVER PERFORMANCE STATE).
     Without this permission, the query fails with error 300 (followed by 297).
   - Shows only what is still in the cache since the last restart or cache clear.
   - Filters to the current database through the plan attribute dbid.
   - The cache text contains leading comments. The column "tag" shows the first
     comment line directly before the statement, in EF Core the tag set with TagWith.
*/
WITH cache AS (
    SELECT qs.query_hash,
           qs.sql_handle,
           qs.plan_handle,
           qs.execution_count,
           qs.total_worker_time  / 1000.0 AS cpu_ms,
           qs.total_elapsed_time / 1000.0 AS duration_ms,
           qs.total_logical_reads         AS logical_reads
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY (SELECT CONVERT(int, pa.value) AS dbid
                 FROM sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
                 WHERE pa.attribute = 'dbid') AS db
    WHERE db.dbid = DB_ID()
)
SELECT TOP (25)
    CONVERT(varchar(20), c.query_hash, 1)          AS query_hash,
    COUNT(DISTINCT c.sql_handle)                   AS texts,
    COUNT(DISTINCT c.plan_handle)                  AS plans,
    SUM(c.execution_count)                         AS executions,
    CAST(SUM(c.cpu_ms)   AS decimal(18, 1))        AS cpu_ms_total,
    CAST(SUM(c.duration_ms) AS decimal(18, 1))     AS duration_ms_total,
    SUM(c.logical_reads)                           AS logical_reads_total,
    MAX(CASE WHEN k.pos > 0
             THEN REPLACE(LEFT(SUBSTRING(st.text, k.pos + 3, 120),
                               ISNULL(NULLIF(CHARINDEX(CHAR(10), SUBSTRING(st.text, k.pos + 3, 120)), 0) - 1, 120)),
                          CHAR(13), '')
        END)                                       AS tag,
    LEFT(MIN(st.text), 300)                        AS sample_text
FROM cache AS c
CROSS APPLY sys.dm_exec_sql_text(c.sql_handle) AS st
CROSS APPLY (SELECT CASE WHEN st.text LIKE N'-- %' THEN 1
                         WHEN st.text LIKE N'(@%' THEN ISNULL(NULLIF(CHARINDEX(N')-- ', st.text), 0) + 1, 0)
                         ELSE 0 END AS pos) AS k
GROUP BY c.query_hash
ORDER BY SUM(c.cpu_ms) DESC
OPTION (MAXDOP 1);

How to read the result:

• texts greater than 1 for the same shape means: the application sends values in the SQL text instead of as parameters. IN lists with constants are typical. Each variant takes up its own space in the plan cache and has to be compiled on its first call.
• plans greater than 1 for one text means: SQL Server chose different plans for the same statement, for example after a statistics update or for other parameter values. Whether that is a problem shows when you compare the plans.
• marker names hints of typical patterns, for example a list as a JSON parameter. That is a suspicion, not proof that Entity Framework generated the statement.
• tag (only in the second query, from the plan cache) shows the comment that the application attached to the statement with TagWith(). If the column is empty, the trail back into the code is missing. Query Store does not store this comment, see below.
• Parameter declaration at the start of the text, for example (@ids4 int,@ids3 int,…)SELECT: that is how Query Store stores parameterized statements. With EF Core 10, a long list of single parameters is typical for Contains() on a list.
• cpu_ms_total is the sort key. A cheap statement that runs a hundred thousand times can cost more than the one slow statement everybody complains about.

Sample output from a reproduced lab run (SQL Server 2025, synthetic data, tags chosen freely, columns shortened). The four rows show the three translations of Contains(): a list as a JSON parameter (EF Core 8 and 9), parameter lists (EF Core 10), and constants (EF Core 7):

Statements in Query Store
query_hash          texts  plans  executions  cpu_ms_total  marker                   sample_text
0xC272B4BDD75093D9  1      1      12          1.2           List as JSON parameter   (@__ids_0 nvarchar(4000))SELECT [p].[Id], ...
0x4B531E19E1CDD5CE  2      2      7           .8            IN list, many texts      (@ids4 int,@ids3 int,@ids2 int,@ids1 int)SELECT ...
0x87C447D1AF3AC850  1      1      3           .4            IN list with parameters  (@ids8 int,@ids7 int, ... ,@ids1 int)SELECT ...
0x1948732EF86250B4  3      3      3           .2            IN list, many texts      SELECT [p].[Id], ... WHERE [p].[Id] IN (1, 2, 3)

Statements in the plan cache
query_hash          texts  plans  executions  tag                 sample_text
0xC272B4BDD75093D9  1      1      12          Catalog-Search      (@__ids_0 nvarchar(4000))-- Catalog-Search
0x87C447D1AF3AC850  1      1      3           Catalog-Search-v10  (@ids1 int, @ids2 int, ...)-- Catalog-Search-v10

Reading it: the last shape in Query Store has three texts and three plans for a single query; the values are in the text. The second row shows a peculiarity: a constant list and a parameter list of the same length have the same shape (query_hash), so the marker column cannot tell them apart. The tag “Catalog-Search” is only in the plan cache.

From Query Store back to the code

Query Store tells you which statement is expensive. It does not tell you which method it comes from. Paging, joins over navigations, or the shape of the parameters are hints, but not a reliable reconstruction of the LINQ expression.

The reliable trail is laid by the application itself: with TagWith("Order overview: open orders"), EF Core writes a comment in front of the statement. Since EF Core 6, TagWithCallSite() adds the file and line of the call. But be careful where you look for it: Query Store stores the statement text without leading comments. In the lab, the tag was missing there on all versions from SQL Server 2017 to 2025. It is in the text in the plan cache, in Extended Events, and in the application's logs. That is why the second query above shows the tag in the column tag, and the first does not. If you want to find the tag in Query Store as well, you need to match on the shape of the statement (query_hash) with the plan cache, as long as the statement is still there. Keep tags fixed and descriptive. Do not write user IDs, timestamps, or other changing values into them: every variant produces its own statement text and thus its own entry in the plan cache.

Query Store stores totals per plan and time interval. It knows no individual executions, no parameter values, and no response time of a particular user. For those questions you need measurement in the application. Together they give the picture: the application shows which operation is slow, and Query Store shows what it costs on the server.

Five common assumptions that are not true

“AsNoTracking() makes the SQL query cheaper.” Change tracking happens in EF, not in SQL Server. With the same SQL and the same parameters, the plan stays the same. In the lab, the generated SQL was character-identical with and without AsNoTracking(). The response time can still change, because EF works differently, and in both directions: in our experiment with many duplicate rows, AsNoTracking() was even 7 to 8% slower than tracking (details). Cost in EF and cost in SQL Server are two different measures.

“Compiled queries save SQL Server the compilation.” EF Core translates a LINQ query only once anyway and remembers the result in its own cache. A compiled query only saves EF the lookup in this cache. SQL Server has its own plan cache; whether it reuses a plan is decided by the SQL text, not by EF.

“Include() is always better than lazy loading.” Include avoids many small queries. With two collections in one query, however, it can transfer a multiple of the rows you actually need: in the lab, 16,257 rows and 15.9 MB for a page with 50 customers, instead of 2,485 rows and 0.6 MB as a split query. A projection to a few columns changes little about this; it only makes the rows narrower. When Include, projection, or split query is the right choice is shown in the first deep dive.

“An EF Core update changes nothing in the database as long as the code stays the same.” EF Core translates the same expressions differently from version to version. A well-documented example is Contains() on a list: constants, a JSON parameter, or several single parameters, depending on the version. Even small changes count: EF Core 10 names parameters more briefly (@city instead of @__city_0). For SQL Server that is a new text, so after the update the affected statements are compiled anew. What this means for the plan cache and for estimates is shown in the second deep dive.

“EnableRetryOnFailure() makes saving safe.” The retry helps with short connection errors. But if the connection breaks exactly during the commit, the application does not know whether the change was saved. A retry can then book twice if the operation is not idempotent. Microsoft describes the problem and four ways to deal with it explicitly (see Sources). If you open your own transactions, you must also run them through CreateExecutionStrategy(), otherwise EF rejects the retry.

EF Core and EF6

Entity Framework 6 and Entity Framework Core are two separate product lines. EF Core is not a successor with the same behavior but a new development. The statements on this page apply to EF Core unless stated otherwise. EF6 (currently 6.5) continues to be supported by Microsoft but is no longer actively developed; since version 6.3 it also runs on .NET Core and .NET. Many existing applications use it. The principles on this page apply there as well, but the generated SQL and the options differ.

A concrete occasion: support for EF Core 8 and EF Core 9 ends on November 10, 2026, together with .NET 8 and .NET 9. The next step is EF Core 10 (supported until November 2028). Many teams are therefore migrating right now. That is the right moment to compare the generated SQL before and after the update, before production does it for you. How to do that is shown in the second deep dive.

What provides lasting relief

• Look at the generated SQL. ToQueryString() shows the SQL of a query without executing it, and LogTo() logs all statements with their duration. A look during development saves many surprises in operation.
• Load only what is needed. A projection to the required columns saves transfer, memory, and often read work. That is not a trick but an architecture decision: which data does this operation really need?
• Tag queries. Fixed tags on the important queries make the way from the plan cache, from Extended Events, and from the logs back into the code short.
• Enable and use Query Store. It preserves plans and run times across restarts and is the basis for comparisons before and after a release.
• Test upgrades under load. Before an EF Core update or a change of the compatibility level, run the most important operations with realistic data and compare SQL, plans, and cost. Change the axes separately: first EF, then the compatibility level.
• Define data types in the model. The length, Unicode setting, and precision of the columns determine the parameter types. If they do not match the column, SQL Server may not be able to use an index as expected.
• Choose the right tool. Set-based changes with ExecuteUpdate() or ExecuteDelete(), a targeted SQL statement, or a stored procedure where LINQ reaches its limits. Entity Framework does not rule that out.
• Widen your view. If the server as a whole is slow, the SQL Server performance guide leads you through the matching diagnostic paths.

In depth: Include, Projection, and Split Query · One Contains(), Four EF Core Versions

Sources

• Microsoft: EF Core and EF6 compared
• Microsoft: EF Core releases and support periods, .NET support policy
• Microsoft: Efficient Querying
• Microsoft: Tracking vs. No-Tracking Queries
• Microsoft: Compiled Queries
• Microsoft: Query Tags
• Microsoft: Connection Resiliency, commit failures, and idempotency
• Microsoft: EF Core 10: simplified SQL parameter names
• Microsoft: Enable the Query Store