Design a Provider-Abstraction Boundary Without Falling into Lowest-Common-Denominator Data Modeling

Learning outcomes

1. The problem: abstraction can either clarify ownership or hide the database

After four lessons it is clear that PostgreSQL and MySQL do not differ only in connection strings. A common reaction is to invent a giant IRepository<T> exposing FindAll, FindBy, Add, and generic specifications until every database feature is inaccessible. That is not portability—it is information loss. DbContext already supplies unit-of-work/repository-like capabilities; the useful abstraction is around the business capability that truly differs.

2. Split portable workflow from native capability

ServiceHub’s ordinary work-order lifecycle—load by ID, validate, change summary/priority, save with the Revision token—can stay provider-neutral because Chapters 01–20 already define those EF semantics. Search, similarity, advanced JSON/range/array queries, and provider-specific bulk/migration features may deserve adapters.

public sealed class WorkOrderService(ServiceHubContext db){    public async Task ReviseSummaryAsync(        int id, Guid expectedRevision, string summary, CancellationToken ct)    {        var workOrder = await db.WorkOrders.SingleAsync(x => x.Id == id, ct);        db.Entry(workOrder).Property(x => x.Revision).OriginalValue = expectedRevision;        workOrder.ReviseSummary(summary);        workOrder.AdvanceRevision();        await db.SaveChangesAsync(ct);    }}

This service benefits from EF’s tracking/concurrency behavior on both providers. There is no need to replace it with generic CRUD just because two databases are supported.

3. Define capability interfaces at the point of real divergence

Suppose PostgreSQL deployments support regex/array search while MySQL deployments use full-text/generated-column or JSON-backed search. The portable application asks for a search result; the provider adapter owns query syntax and indexes.

public interface IWorkOrderSearch{    Task<IReadOnlyList<WorkOrderHit>> SearchAsync(        WorkOrderSearchRequest request,        CancellationToken ct);}public sealed record WorkOrderSearchRequest(    string Text,    int Limit,    string? RequiredToken = null);public sealed record WorkOrderHit(int Id, string WorkOrderNumber, double Score);

The interface does not mention ILIKE, regex operators, arrays, MySQL JSON functions or Npgsql types. Each implementation may use those features internally and can expose provider-specific diagnostics for tests/operations.

4. Prefer deployment-time capability selection to scattered provider conditionals

One anti-pattern checks Database.ProviderName in dozens of repositories. That spreads database knowledge across the application and makes unsupported combinations easy to create. Bind the adapter once during composition.

if (configuration["Database:Provider"] == "Postgres"){    services.AddDbContext<ServiceHubContext>(o => o.UseNpgsql(pgConnection));    services.AddScoped<IWorkOrderSearch, PostgresWorkOrderSearch>();}else if (configuration["Database:Provider"] == "MySql"){    services.AddDbContext<ServiceHubContext>(o => o.UseMySQL(mySqlConnection));    services.AddScoped<IWorkOrderSearch, MySqlWorkOrderSearch>();}else{    throw new InvalidOperationException("Unsupported ServiceHub database provider.");}

Startup should also validate that the provider package/database version/migration assembly matches the configured product profile.

5. Capability flags are useful only when the product truly varies at runtime

If the same binary can legitimately run with multiple providers and some feature is optional, a small immutable capability descriptor can drive UI/feature selection. Do not probe arbitrary database features on every request.

public sealed record DatabaseCapabilities(    string Provider,    bool RegexSearch,    bool NativeArrays,    bool NativeRanges,    bool JsonPathIndexing,    bool ProviderSpecificBulkPath);// Construct once from the supported deployment profile and verified server version.// It is not authorization and it is not a substitute for integration tests.

Keep security/tenant authorization separate. “Database supports regex” must never decide whether a user may see a row.

6. Do not leak Npgsql/MySQL-specific CLR types into shared domain contracts accidentally

If NpgsqlRange<DateTime> appears in the core application DTO, the application is no longer meaningfully provider-neutral. That may be acceptable for a PostgreSQL-only product, but it should be intentional. For multi-provider ServiceHub, expose MaintenanceWindow(StartUtc, EndUtc) in the domain and translate it inside the PostgreSQL adapter to a range query while MySQL uses two columns.

public readonly record struct MaintenanceWindow(    DateTime StartUtc,    DateTime EndUtc){    public bool Contains(DateTime instantUtc)        => instantUtc >= StartUtc && instantUtc < EndUtc;}

The provider-specific persistence model may differ radically while the business invariant remains stable.

7. Observability must survive the abstraction

A provider adapter that swallows SQL, timing, errors, migration version and provider name makes production diagnosis worse. Reuse Chapter 18’s logs/traces/query tags and expose stable operation names rather than raw sensitive SQL as metric labels.

var query = db.WorkOrders    .TagWith("ServiceHub.Search.Postgres.v1")    .Where(w => EF.Functions.ILike(w.CustomerName, pattern))    .Select(/* bounded read model */)    .Take(request.Limit);logger.LogInformation(    "WorkOrder search provider {Provider} limit {Limit}",    db.Database.ProviderName,    request.Limit);

Keep raw SQL and parameter values out of high-cardinality production metrics. Correlate a trace exemplar to logs and database plan evidence when diagnosing a slow provider-specific operation.

8. Failure case: lowest-common-denominator repository blocks useful features and still is not portable

A generic repository forbids provider functions, JSON, arrays, range predicates, set-based writes and raw SQL. Teams reimplement them with in-memory filtering, N+1 loops, serialized blobs, or hidden casts. The app becomes slower and more fragile while migrations and collations still differ underneath.

Repair: keep EF Core directly available in persistence/application services for portable workflows, define focused adapters for real divergence, and require provider integration tests. Portability comes from controlled seams and evidence, not from banning the database.

9. Contract tests define what “supported provider” means

A release should not claim PostgreSQL/MySQL support because both contexts start. Encode support as tests:

10. Mandatory lab: design and prove the boundary

1. Keep WorkOrderService provider-neutral and run its concurrency-aware update contract on PostgreSQL and MySQL.
2. Define IWorkOrderSearch and implement PostgresWorkOrderSearch using one Npgsql-native feature (for example regex/ILIKE/array search).
3. Implement MySqlWorkOrderSearch using a MySQL-native text/JSON/collation approach verified by current provider/database behavior.
4. Bind the correct implementation once in the composition root and fail fast on an unsupported provider value.
5. Add a DatabaseCapabilities descriptor only for a feature that genuinely varies; write a test that prevents it from being used as authorization.
6. Run shared portable contract tests on PostgreSQL 18.6 and MySQL 8.4 LTS; run adapter-specific tests only on their owners.
7. Capture query tags, generated SQL, parameters and one plan for each search adapter.
8. Write an ADR-style decision: which semantics are portable, which features are provider-native, which migration stream owns each schema extension, and what must pass before a provider is marketed as supported.

11. Production judgment and bridge to Chapter 22

A healthy provider abstraction is narrow and evidence-backed. It preserves EF Core’s useful unit-of-work/query semantics, keeps native database strengths available, and localizes divergence where the product actually needs it. It also states when portability is not worth the cost. With provider boundaries explicit, Chapter 22 can introduce global query filters and multi-tenancy without accidentally confusing tenant isolation with a provider abstraction or assuming filters alone are authorization.

Authoritative references

Portability architecture should build on EF’s existing DbContext capabilities and current provider behavior, not add abstraction for its own sake.

• EF Core architecture and DbContext — DbContext unit-of-work/lifetime semantics
• EF Core testing with the database — real-provider integration test guidance
• EF Core database providers — provider compatibility and feature ownership
• Npgsql EF Core documentation — PostgreSQL provider-specific capabilities
• MySQL Connector/NET EF Core documentation — Oracle MySQL EF Core provider boundary
• Pomelo provider compatibility matrix — MySQL/MariaDB provider scope and published EF compatibility