LINQ Translation Differences, Function Mapping, Generated SQL, and Provider Test Matrices

Learning outcomes

1. The problem: LINQ syntax is not the execution contract

Two contexts can accept the same Where/OrderBy/Select expression and emit materially different SQL. Different collations can even return different rows. The portability contract is therefore: same intended business result under declared provider/database settings, with acceptable generated SQL and plans—not identical SQL text.

var page = await db.WorkOrders    .Where(w => w.Priority >= minimumPriority)    .OrderBy(w => EF.Property<DateTime>(w, "CreatedUtc"))    .ThenBy(w => w.Id)    .Select(w => new { w.Id, w.WorkOrderNumber, w.CustomerName })    .Take(50)    .ToListAsync(ct);

Run that query against both providers, compare results, ToQueryString(), command parameters and plans. Do not compare SQL strings for equality; compare behavior and provider-specific quality criteria.

2. Common translation still exposes different SQL dialects

PostgreSQL and MySQL both support filtering, ordering, projection and pagination, but quoting, casts, LIMIT/OFFSET syntax, date functions, string functions and parameter types differ. That is expected.

var query = db.WorkOrders    .Where(w => w.CustomerName.StartsWith(prefix))    .OrderBy(w => w.CustomerName)    .ThenBy(w => w.Id)    .Select(w => new { w.Id, w.CustomerName })    .Take(25);var sql = query.ToQueryString();_output.WriteLine(db.Database.ProviderName);_output.WriteLine(sql);var rows = await query.ToListAsync(ct);

3. Npgsql deliberately translates PostgreSQL-specific .NET patterns

Npgsql’s translation surface is broader in areas where PostgreSQL has native operators. For example, .NET regular expressions can translate to PostgreSQL regex operators, and Npgsql exposes EF.Functions.ILike for case-insensitive pattern matching. These are excellent PostgreSQL capabilities and terrible candidates for pretending to be provider-neutral.

public async Task<int[]> FindIdsAsync(    ServiceHubContext pg,    string regex,    CancellationToken ct){    return await pg.WorkOrders        .Where(w => Regex.IsMatch(w.CustomerName, regex))        .OrderBy(w => w.Id)        .Select(w => w.Id)        .ToArrayAsync(ct);}// Alternative Npgsql-native pattern API:// .Where(w => EF.Functions.ILike(w.CustomerName, pattern))

The adapter’s contract should say “regex search” or “case-insensitive pattern search.” A MySQL adapter can implement that contract using its supported regex/collation/functions if the chosen provider translates them, or use a different query path. Do not leak ILike into portable application code.

4. MySQL string semantics are governed by collation as much as LINQ spelling

A shared ==, StartsWith, or Contains can return different matches if PostgreSQL and MySQL columns use different collations. MySQL deployments commonly choose Unicode collations with different case/accent sensitivity characteristics. Pin the business rule in schema/test configuration.

var ids = await db.WorkOrders    .Where(w => w.CustomerName == "Acme")    .OrderBy(w => w.Id)    .Select(w => w.Id)    .ToArrayAsync(ct);// Assert the result expected by the product's declared comparison rule.// If providers differ, fix the configured collation or make the capability explicit.

5. Date/time and null behavior require explicit fixtures

EF Core adds null compensation in some cases to align C# two-valued semantics with SQL three-valued logic, but provider translations and database types remain different. Timestamp/time-zone behavior is especially provider-specific. Build fixtures containing nulls, boundary instants, offsets and daylight-saving transitions where the domain depends on them.

var due = await db.WorkOrders    .Where(w => w.AssignedTechnicianId == null || w.Priority >= 3)    .OrderBy(w => EF.Property<DateTime>(w, "CreatedUtc"))    .ThenBy(w => w.Id)    .Select(w => new { w.Id, w.AssignedTechnicianId })    .ToListAsync(ct);

Assert results against a deterministic seed. If a provider stores or truncates a temporal value differently, that is a mapping contract to fix—not a flaky-test excuse.

6. Parameter types and collection predicates can change plans

Even when values are parameterized safely, provider parameter type/size choices can influence plan selection and index usage. EF Core 10 also has specific collection-parameter behavior; providers may translate membership tests differently. Capture command parameters from structured logs/interceptors and inspect the real database plan for hot queries.

var selectedIds = request.WorkOrderIds.Take(200).ToArray();var rows = await db.WorkOrders    .Where(w => selectedIds.Contains(w.Id))    .Select(w => new { w.Id, w.WorkOrderNumber })    .ToListAsync(ct);

For very large input sets, do not assume a giant Contains list is optimal on either engine. PostgreSQL arrays/unnest/temp tables and MySQL temporary tables/bulk staging are provider-specific alternatives that should be benchmarked.

7. Failure case: a provider-specific expression escapes into the shared query layer

A shared service uses EF.Functions.ILike because it works well on PostgreSQL. The MySQL test either fails translation or cannot reproduce the same semantics. A developer “fixes” it with AsEnumerable(), causing the full candidate table to stream into the application.

var rows = db.WorkOrders    .AsEnumerable() // database work stops here    .Where(w => Regex.IsMatch(w.CustomerName, pattern))    .Take(25)    .ToList();

Repair: move the capability into a provider adapter with a server-translated implementation on each supported provider. If no equivalent exists, document that capability as provider-specific. Client evaluation is acceptable only after an explicit bounded server query where the row/byte limit is proven.

8. Plans tell you whether semantic portability is also operationally acceptable

Once result assertions pass, inspect database-side work. PostgreSQL and MySQL expose different EXPLAIN formats, statistics and plan operators, but both can reveal full scans, poor selectivity, sort spills or missing index opportunities.

-- PostgreSQL lab (use ANALYZE only when executing the query is safe)EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)SELECT ...;-- MySQL labEXPLAIN FORMAT=TREESELECT ...;

Do not paste ToQueryString() into a plan tool and claim you benchmarked EF. Use the actual parameter values/types, realistic data distribution and normal connection topology.

9. Mandatory lab: turn one query suite into a provider matrix

1. Seed the same deterministic ServiceHub dataset into PostgreSQL and MySQL.
2. Run shared tests for equality/nulls, StartsWith/Contains, deterministic pagination, aggregate/grouping, correlated Any/Exists and a bounded Contains(list).
3. For every query capture ProviderName, ToQueryString(), command parameters, row IDs/count/order and elapsed command time.
4. Add one Npgsql-only regex/ILike test behind a PostgreSQL capability adapter.
5. Add one MySQL-specific text/JSON function or collation test behind the MySQL adapter only after verifying current provider translation.
6. Generate PostgreSQL EXPLAIN and MySQL EXPLAIN for one hot query and record the chosen index/path.
7. Introduce one deliberate unsupported provider expression; assert translation failure, then repair it without unbounded AsEnumerable.
8. Save the matrix as test artifacts and drop disposable databases.

10. Production judgment and bridge

Portable LINQ is a tested subset, not a theoretical language intersection. Keep shared queries where results and performance are acceptable on every supported provider, and move native functions into named adapters. Provider upgrades must rerun the matrix because translation is implementation, not a permanent language guarantee. Lesson 4 applies the same discipline to migrations and DDL.

Authoritative references

Translation is provider implementation; use current provider translation and database plan documentation.

• Npgsql translations — PostgreSQL-specific translations including regex, ILIKE and aggregates
• EF Core client vs server evaluation — translation failure and explicit client boundaries
• EF Core efficient querying — query shape, projection and plan evidence
• MySQL Connector/NET EF Core support — Oracle provider capabilities and limitations
• PostgreSQL EXPLAIN — PostgreSQL plan inspection
• MySQL EXPLAIN — MySQL execution-plan inspection