Provider-Specific SQL Inspection: When EF Abstractions Leak and How to Keep Them Intentional

Learning outcomes

1. The problem: “portable LINQ” ends where database semantics begin

EF Core gives ServiceHub one LINQ programming model, but providers translate expression trees into different SQL dialects and capabilities. SQL Server has APPLY, collations, temporal operators, full-text search, OFFSET/FETCH, SQL Server date functions, native JSON on newer compatibility levels, and provider-specific DML shapes. The goal is not to avoid those features; it is to keep the dependency intentional and observable.

2. Pagination becomes OFFSET/FETCH and still needs deterministic ordering

Skip/Take with ordering normally becomes SQL Server OFFSET ... FETCH. The relational cost of deep offset pagination remains: SQL Server still has to navigate/order rows before discarding earlier ones. Chapter 08 keyset-pagination guidance still applies.

var page = await db.SqlServerWorkOrders    .OrderByDescending(w => w.OpenedUtc)    .ThenByDescending(w => w.Id)    .Skip(offset)    .Take(pageSize)    .Select(w => new { w.Id, w.WorkOrderNumber, w.OpenedUtc })    .ToListAsync(ct);

ORDER BY [w].[OpenedUtc] DESC, [w].[work_order_id] DESCOFFSET @__offset ROWS FETCH NEXT @__pageSize ROWS ONLY

3. String and collation semantics leak through equality and LIKE

SQL Server comparisons follow database/column collation. EF.Functions.Like produces SQL LIKE; EF.Functions.Collate emits COLLATE. A C# string comparison therefore does not magically retain .NET ordinal semantics. Query-level collation can also change index eligibility.

var results = await db.SqlServerWorkOrders    .Where(w => EF.Functions.Like(w.CustomerName, prefix + "%"))    .Where(w => EF.Functions.Collate(        w.WorkOrderNumber,        "Latin1_General_100_BIN2") == exactNumber)    .ToListAsync(ct);

WHERE [w].[CustomerName] LIKE @__pattern  AND [w].[WorkOrderNumber] COLLATE Latin1_General_100_BIN2 = @__exactNumber

4. Date/time members translate to SQL Server functions

Provider function mappings are versioned. For example, DateTime.UtcNow maps to SQL Server UTC time functions, and EF Core 10 added translations for microsecond/nanosecond members. Use these translations only when the database-side clock/precision is the intended semantic.

var stale = await db.SqlServerWorkOrders    .Where(w => w.OpenedUtc < DateTimeOffset.UtcNow.AddHours(-4))    .ToListAsync(ct);

If the domain needs one authoritative “now” across multiple operations, capture it once or use a transaction/database expression deliberately. Repeated server-function calls and application clocks are not automatically identical.

5. Correlated collection selectors can become CROSS APPLY or OUTER APPLY

Some LINQ shapes reference the outer element inside a collection selector rather than a simple join predicate. SQL Server can translate these with CROSS APPLY or OUTER APPLY. SQLite does not support APPLY, which is why Chapter 09 treated this translation as provider-dependent.

var labels = await db.SqlServerWorkOrders    .SelectMany(        w => db.Technicians.Select(t => new        {            w.Id,            Label = w.WorkOrderNumber + ":" + t.DisplayName        }))    .Take(20)    .ToListAsync(ct);

SELECT TOP(@__p_0) ...FROM [work_orders] AS [w]CROSS APPLY (    SELECT [w].[WorkOrderNumber] + N':' + [t].[DisplayName] AS [Label]    FROM [technicians] AS [t]) AS [x]

Do not use APPLY because it is “advanced.” Inspect cardinality and plan shape; a simpler join or projection may be clearer and cheaper.

6. JSON translation depends on storage and compatibility

EF Core 10 can translate member access inside mapped JSON complex values. On SQL Server 2025/compatibility 170 native JSON, typed JSON_VALUE(... RETURNING ...) forms become available. Text-backed JSON on older compatibility uses different expressions/casts. JSON indexes modeled by EF belong to EF Core 11, not this baseline.

var priorityDispatch = await db.SqlServerWorkOrders    .Where(w => w.Dispatch.ServiceTier >= 3)    .Select(w => new { w.Id, w.Dispatch.Region, w.Dispatch.Address.City })    .ToListAsync(ct);

WHERE JSON_VALUE([w].[Dispatch], '$.ServiceTier' RETURNING int) >= 3

7. Set operations preserve SQL bag/set semantics

Concat maps to UNION ALL semantics, while Union removes duplicates. Intersect and Except rely on SQL Server set semantics, type coercion and collation. Normalize compatible projection shapes before combining them, as taught in Chapter 09.

var urgent = db.SqlServerWorkOrders    .Where(w => w.Priority >= 4)    .Select(w => new { w.Id, w.WorkOrderNumber });var escalated = db.SqlServerWorkOrders    .Where(w => w.ProviderEscalationNote != null)    .Select(w => new { w.Id, w.WorkOrderNumber });var combined = await urgent.Union(escalated).ToListAsync(ct);

Use ToQueryString() to confirm UNION versus UNION ALL, then inspect the plan if duplicate elimination becomes expensive.

8. Set-based DML exposes SQL Server update/delete shape

ExecuteUpdate and ExecuteDelete bypass normal change-tracker synchronization and execute immediately, as Chapter 12 established. SQL Server typically emits direct set-based DML. Provider capability still determines which setters/joins can translate.

var affected = await db.SqlServerWorkOrders    .Where(w => w.Status == WorkOrderStatus.Open && w.OpenedUtc < cutoff)    .ExecuteUpdateAsync(setters => setters        .SetProperty(w => w.Status, WorkOrderStatus.Closed), ct);

UPDATE [w]SET [w].[Status] = @__ClosedFROM [work_orders] AS [w]WHERE [w].[Status] = @__Open AND [w].[OpenedUtc] < @__cutoff;

If rowversion business concurrency must be enforced for a bulk update, include the token predicate yourself and inspect affected; ExecuteUpdate does not automatically perform tracked-entity concurrency conflict detection for a whole set.

9. Failure case: SQL Server full-text extension leaks into the SQLite path

A shared repository method adds EF.Functions.FreeText because it works well on SQL Server. The same method is then exercised by the course SQLite test provider. The SQLite provider cannot translate the SQL Server-specific function, so the “portable repository” was never portable.

public IQueryable<SqlServerWorkOrder> SearchWithSqlServerFullText(    SqlServerServiceHubContext db,    string term){    return db.SqlServerWorkOrders        .Where(w => EF.Functions.FreeText(w.CustomerName, term));}

Repair: move the method behind an explicitly SQL Server-specific query service/provider adapter and keep an integration test against SQL Server. Provide a separate portable fallback only if the product requirement needs one; do not silently degrade a full-text search into a different semantic.

10. Evidence stack: ToQueryString is necessary but not sufficient

For each provider-specific query, collect evidence at multiple layers:

Never conclude that “EF is slow” from SQL text alone. Never conclude that a provider-specific query is wrong solely because another provider cannot translate it. The architecture question is whether the dependency is intentional, isolated, tested, and operationally understood.

11. Mandatory lab: build a SQL Server translation notebook

1. Use the disposable SQL Server provider database and seed the same deterministic ServiceHub rows used in earlier chapters.
2. For one pagination, collation, date, APPLY, JSON, set-operation, and ExecuteUpdate query, save the LINQ expression and ToQueryString() output.
3. Run each query with a TagWith("ServiceHub:SqlServerDeepDive:...") tag and capture the real command log.
4. For two performance-relevant queries, capture actual SQL Server plans and logical reads; note parameter values/distribution without storing sensitive data.
5. Run the SQL Server full-text example only after creating/validating the required full-text infrastructure; otherwise keep it as a translation-boundary observation.
6. Execute the SQL Server-only method under the SQLite integration provider and record the translation failure as an expected architectural test.
7. Document which methods stay in the portable query layer and which live in SqlServerServiceHubQueries.

12. Production judgment and bridge to Chapter 20

EF Core abstractions are most valuable when they make intent clear while keeping provider behavior inspectable. Use SQL Server-specific capabilities when they solve a measured product/database problem, but isolate them, pin provider compatibility, inspect SQL/plans, and test failure modes. Do not promise identical null/collation/JSON/function/DDL behavior across providers. Chapter 20 now applies the same discipline to SQLite, including its type, concurrency, DDL, locking and translation constraints.

Authoritative references

SQL translation is versioned behavior. Re-check the provider function mapping and query documentation when upgrading EF Core or SQL Server compatibility.

• SQL Server function mappings - EF Core — provider translations for strings, dates, aggregates and SQL Server functions
• SQL Server provider - EF Core — compatibility levels, Azure SQL and provider feature boundary
• Complex query operators - EF Core — JOIN/APPLY/grouping translation patterns
• Pagination - EF Core — offset/keyset pagination and ordering
• Efficient querying - EF Core — projection, indexes and database plan evidence
• Executing raw SQL - EF Core — explicit SQL escape hatches and parameterization boundaries