Identity/Sequence Strategies, Case Sensitivity, Types, JSON, Arrays, and Database-Specific Extensions

Learning outcomes

1. The problem: one CLR model does not imply one physical data model

WorkOrder.Id is an int, Revision is a Guid, and structured ServiceHub values can be represented as JSON. Those C# facts do not say whether keys come from PostgreSQL identity/sequence or MySQL auto-increment, whether a set of tags belongs in an array or join table, how case comparison works, or which JSON operators/indexes exist. Portability starts by naming the portable requirement and then choosing each engine’s physical realization.

2. Value generation: similar goal, different mechanisms

PostgreSQL 10+ has SQL-standard identity columns backed by sequences, and Npgsql uses “identity by default” as its normal generated-int strategy. It also exposes explicit sequences and HiLo. MySQL uses AUTO_INCREMENT for the familiar generated integer key path. These choices affect migrations, seed values, bulk/import workflows and cross-provider key assumptions.

modelBuilder.UseIdentityByDefaultColumns();modelBuilder.Entity<WorkOrder>()    .Property(x => x.Id)    .UseIdentityAlwaysColumn();// Advanced alternative only when measured/justified:modelBuilder.Entity<ImportBatch>()    .Property(x => x.Id)    .UseHiLo("servicehub_hilo");

// Portable model intent:builder.Property(x => x.Id).ValueGeneratedOnAdd();// PostgreSQL DDL can become:// integer GENERATED BY DEFAULT AS IDENTITY// MySQL DDL can become:// int NOT NULL AUTO_INCREMENT

Do not write application logic that assumes “the next integer” can be fetched the same way on every engine. EF abstracts value generation for ordinary inserts, but schema/admin/import semantics still leak.

3. Identifier and string case behavior are different questions

PostgreSQL folds unquoted identifiers to lowercase; Npgsql normally quotes identifiers generated from mixed-case CLR names. MySQL identifier case behavior can also vary by operating system/server settings. Separately, string equality/order depends on database/column collation. Therefore “PostgreSQL is case-sensitive, MySQL is case-insensitive” is too crude to be a portability rule.

// Portable application rule: customer code comparison must be deterministic.// Configure provider-specific column/database collation in provider model code,// then test equality, ordering and uniqueness on each engine.var q = db.WorkOrders    .Where(w => w.CustomerName == input)    .OrderBy(w => w.CustomerName);

The test should verify rows returned and the index/plan behavior. Do not fix a collation mismatch with ToLower() everywhere; that can change index use and language semantics.

4. PostgreSQL arrays are a real relational-provider extension

Npgsql maps CLR arrays and List<T> to PostgreSQL array columns and translates many operations. For a provider-specific search profile, an array can be more honest than encoding tags into a delimited string. It is not portable to MySQL as an equivalent native array column.

public sealed class PgSearchProfile{    public int WorkOrderId { get; private set; }    public string[] SearchTokens { get; private set; } = [];}protected override void OnModelCreating(ModelBuilder modelBuilder){    modelBuilder.Entity<PgSearchProfile>()        .Property(x => x.SearchTokens)        .HasColumnType("text[]");}

var matches = await pg.Set<PgSearchProfile>()    .Where(x => x.SearchTokens.Contains(token))    .Select(x => x.WorkOrderId)    .ToListAsync(ct);

MySQL portability choices include a normalized child table or JSON array plus provider-specific JSON predicates. Pick based on query/update/index needs, not on a desire for identical DDL.

5. jsonb, JSON, and EF Core 10 structured values are not one universal feature

PostgreSQL has jsonb with rich operators and indexes; Npgsql 10 fully supports EF Core 10 JSON complex types. MySQL has a native JSON type and its own JSON functions/indexing strategies. The portable semantic question is “what structured value do we store and query?”; the physical query/index path is provider-specific.

public sealed record ProviderAddress(    string Line1,    string City,    string Region,    string PostalCode);public sealed class ProviderProfile{    public int Id { get; set; }    public ProviderAddress Address { get; set; } = new("", "", "", "");    public string ProviderMetadataJson { get; set; } = "{}";}modelBuilder.Entity<ProviderProfile>(b =>{    b.ComplexProperty(x => x.Address, json => json.ToJson());});// Npgsql maps the JSON complex value to jsonb in its EF10 support.

// Keep the same provider-lab entity, but put MySQL JSON configuration// in the MySQL provider project.modelBuilder.Entity<ProviderProfile>(b =>{    b.Property(x => x.ProviderMetadataJson)        .HasColumnType("json");});// Then inspect migration SQL and ToQueryString before depending on// a specific JSON member translation or generated-column index.

6. PostgreSQL range/network/native types can be worth a deliberate portability seam

Npgsql directly maps PostgreSQL types such as ranges and network addresses. A ServiceHub maintenance window is naturally a tstzrange; an allow-list network can be inet. Flattening these to strings sacrifices server validation and operators. The portability boundary should expose the business capability, not the physical type.

public sealed class PgMaintenanceWindow{    public long Id { get; private set; }    public NpgsqlRange<DateTime> WindowUtc { get; private set; }    public IPAddress? SourceNetwork { get; private set; }}

A MySQL implementation could use start_utc/end_utc columns plus a check constraint, and a textual/binary network representation. Contract tests should assert “contains instant” and overlap semantics—not insist both providers store the same bytes.

7. MySQL/MariaDB charset, collation, JSON, and generated columns deserve their own migration evidence

Oracle’s MySQL provider can configure character sets/collations through code-first model configuration, and MySQL supports generated columns and JSON. MariaDB has overlapping but not identical syntax/features. This is another reason a MySQL-compatible label is not enough to share an unreviewed migration stream.

SELECT @@character_set_server,       @@collation_server,       VERSION();SHOW CREATE TABLE work_orders;

When a generated column is introduced to index a JSON-derived value, capture the exact provider migration DDL and run it on the target MySQL release. Do not copy the same DDL into MariaDB because product names sound compatible.

8. Failure case: “portable” strings erase useful database semantics

A team stores PostgreSQL arrays, ranges, network addresses, and JSON all as arbitrary strings to keep one provider-neutral entity. Validation moves to application code, malformed values bypass constraints, search predicates stop using native operators/indexes, and the “portable” code becomes slower and more complex.

Repair: keep the core aggregate portable where its semantics are genuinely shared, then add provider-specific read/search/storage models behind adapters. The adapter is allowed to use text[], jsonb, range or MySQL generated columns when the supported product intentionally depends on those capabilities.

9. Mandatory lab: build one portable core plus two native extension examples

1. Apply the same minimal WorkOrder model to PostgreSQL 18.6 and MySQL 8.4 LTS; capture DDL for generated IDs, Guid, text, timestamps and JSON metadata.
2. Insert identical domain rows and verify generated-key propagation on both providers.
3. Choose an explicit case/collation business rule for CustomerName; configure and test equality/order/uniqueness on both engines.
4. Add a PostgreSQL-only text[] search-token projection and prove one translated containment query plus its plan.
5. Add one MySQL-only generated/JSON-search projection or provider metadata table; capture exact DDL and EXPLAIN.
6. Document how the alternate provider would implement the same business capability without demanding identical physical types.
7. Drop the provider-specific lab objects/databases.

10. Production judgment and bridge

Portability is semantic, not textual. Shared EF entities are useful where both databases support the same invariants; provider-native types are valuable where they buy correctness or performance. Keep native choices in named provider modules, review their migrations, and test the business contract on every supported engine. Lesson 3 now applies the same principle to LINQ translation and generated SQL.

Authoritative references

Use provider and database documentation together when a model depends on native types or value generation.

• Npgsql value generation — PostgreSQL identity, sequence, HiLo and UUID strategies
• Npgsql array mapping — CLR arrays/List mapping and translation
• Npgsql type mapping — PostgreSQL-specific type support including network/native types
• Npgsql ranges and multiranges — range modeling and translated operations
• MySQL Connector/NET EF Core character sets/collations — Oracle provider code-first charset/collation configuration
• MySQL 8.4 character sets and collations — engine text comparison/storage semantics