SQLite Type Affinity vs CLR Types: Mappings, Conversions, Precision, and Unsupported Operations

Learning outcomes

1. The problem: a C# type is not a SQLite storage contract

ServiceHub has deliberately used SQLite as its free local baseline, but earlier chapters also exposed several places where the provider cannot behave like SQL Server. The most visible example is WorkOrder.OpenedUtc : DateTimeOffset: EF can persist and read it, yet SQLite has no native DateTimeOffset type and the provider cannot treat every comparison/order operation as a normal database date operation. Chapter 20 makes those leaks the subject instead of hiding them.

2. Start by asking SQLite what it actually stored

For this chapter create a disposable database such as servicehub-sqlite-deepdive.db. The EF provider package is Microsoft.EntityFrameworkCore.Sqlite 10.0.11. Do not hard-code an SQLite native-engine version from the NuGet version: record the engine actually loaded at runtime with sqlite_version().

dotnet add src/ServiceHub.EfLab package Microsoft.EntityFrameworkCore.Sqlite --version 10.0.11dotnet add src/ServiceHub.EfLab package Microsoft.EntityFrameworkCore.Design --version 10.0.11dotnet tool update --local dotnet-ef --version 10.0.11dotnet --infodotnet ef --version

await using var connection = new SqliteConnection(    "Data Source=servicehub-sqlite-deepdive.db;Foreign Keys=True");await connection.OpenAsync(ct);await using var command = connection.CreateCommand();command.CommandText = """SELECT sqlite_version();PRAGMA journal_mode;PRAGMA foreign_keys;""";// Execute these as separate commands/readers in production code; the SQL is grouped// here only as a checklist of facts the lab must capture.

The native SQLite version, journal mode, and foreign-key setting are runtime facts. Record them beside EF/.NET package versions whenever a behavior depends on the engine rather than only on EF.

3. Microsoft.Data.Sqlite ultimately binds four primitive non-null types

The driver maps many .NET types to SQLite, but the resulting database values are still INTEGER, REAL, TEXT, or BLOB. The default mappings are intentionally readable and portable inside SQLite, not identical to server-engine native types.

SELECT    name,    type,    "notnull",    dflt_value,    pkFROM pragma_table_info('sqlite_type_probes');SELECT    typeof(estimated_cost) AS cost_storage,    typeof(scheduled_at) AS time_storage,    typeof(correlation_id) AS guid_storage,    quote(estimated_cost) AS cost_literalFROM sqlite_type_probesLIMIT 5;

4. Use an isolated type-probe entity instead of mutating WorkOrder just to experiment

The course domain already has production-relevant mappings. Create one disposable probe table so type experiments do not silently become ServiceHub schema decisions.

public sealed class SqliteTypeProbe{    public int Id { get; set; }    public decimal EstimatedCost { get; set; }    public DateTimeOffset ScheduledAt { get; set; }    public TimeSpan SlaWindow { get; set; }    public ulong ExternalCounter { get; set; }    public Guid CorrelationId { get; set; }}modelBuilder.Entity<SqliteTypeProbe>(b =>{    b.ToTable("sqlite_type_probes");    b.HasKey(x => x.Id);});

After inserting deterministic edge values, inspect typeof(), quote(), generated parameters, and round-trip values. The goal is not “prove SQLite can store everything”; it is to identify which operations remain safe and which need a different database representation.

5. DateTimeOffset: persist if needed, but do not build SQLite queue ordering around it

The EF SQLite limitation documentation explicitly calls out DateTimeOffset. Equality can be supported, but ordinary comparison/ordering is not a native provider strength. This is why Chapter 04 added the UTC DateTime shadow property CreatedUtc for mandatory server-side ordering.

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

// Keep this as a translation test, not a production queue query.var unsupported = db.WorkOrders.OrderBy(w => w.OpenedUtc);Console.WriteLine(unsupported.ToQueryString());

Modern EF does not silently move a non-translatable ordering predicate to the client. Treat a translation failure as evidence that the representation/query shape is wrong for this provider. If instants are the domain concern, normalize to UTC DateTime or another deliberately ordered representation.

6. decimal: choose precision semantics before choosing storage

Microsoft.Data.Sqlite stores decimal as TEXT by default because REAL is lossy. EF Core 9+ also registers helper functions such as ef_compare, ef_add, ef_sum, and ef_avg so many decimal expressions can execute server-side. Those helpers improve EF translation; they do not create a native SQLite fixed-precision numeric type.

var money = new ValueConverter<decimal, long>(    value => checked((long)decimal.Round(value * 100m, 0,        MidpointRounding.ToEven)),    stored => stored / 100m);builder.Property(x => x.EstimatedCost)    .HasConversion(money);

7. TimeSpan and ulong need equally explicit range/query decisions

TimeSpan defaults to TEXT and ulong is bound through INTEGER even though SQLite integer storage is signed. If the application must order or perform arithmetic on durations, storing ticks as long can be clearer. If a domain counter may exceed long.MaxValue, do not pretend SQLite INTEGER can hold every ulong.

builder.Property(x => x.SlaWindow)    .HasConversion(        value => value.Ticks,        stored => TimeSpan.FromTicks(stored));

For unsigned counters, either enforce a signed-64-bit range, use another representation with explicit ordering rules, or choose a database whose numeric type matches the domain. The right answer is driven by operations and constraints, not by whether SaveChanges succeeds for a sample value.

8. Facets such as MaxLength and precision do not become rigid SQLite enforcement automatically

EF model facets still matter for metadata, migrations, validation libraries, and portability. But ordinary SQLite does not enforce declared string length or decimal precision/scale the way SQL Server does. If the database itself must reject invalid values, add an actual CHECK constraint or use another suitable engine feature and test it.

builder.ToTable("work_orders", table =>{    table.HasCheckConstraint(        "CK_work_orders_customer_name_length",        "length(customer_name) BETWEEN 1 AND 160");});

Adding the check constraint itself becomes a SQLite migration/rebuild concern, which is the subject of Lesson 2.

9. Failure case: declaring a numeric-looking type and assuming SQLite will enforce it

A developer sees decimal(18,2) in a migration and assumes the database will reject an arbitrary text value. A manual integration/import path inserts incompatible data, and later EF queries behave unpredictably because the row does not match the application assumption.

SELECT id,       estimated_cost,       typeof(estimated_cost)FROM sqlite_type_probesWHERE typeof(estimated_cost) <> 'text';

Repair: define the actual database invariant. If the column must have numeric behavior, choose an appropriate representation and add constraints/tests. Validate every non-EF ingestion path as well; an ORM mapping cannot police arbitrary SQL written by other tools.

10. Mandatory lab: map, store, query, and inspect type evidence

1. Copy the disposable ServiceHub SQLite lab file before experimenting; do not use the main course database.
2. Create SqliteTypeProbe with decimal, DateTimeOffset, TimeSpan, ulong and Guid values near meaningful boundaries.
3. Record sqlite_version(), PRAGMA table_info, typeof(), and quote() results.
4. Call ToQueryString() for equality, range, arithmetic and ordering expressions. Record which translate and the helper functions that appear.
5. Keep WorkOrder.OpenedUtc as DateTimeOffset, but prove the established CreatedUtc : DateTime shadow property is the SQLite server-ordering path.
6. Implement one alternate representation (scaled integer money or ticks duration) in the probe model, create/review the migration, and compare SQL/index behavior.
7. Run PRAGMA integrity_check, verify row counts, and delete the disposable database afterward.

11. Production judgment and bridge

SQLite is not “weakly typed EF Core”; it is a distinct database with a dynamic type system and provider-specific translations. Keep the convenient CLR model only where its database representation supports the required predicates, ordering, precision and interoperability. Prefer explicit UTC instants, scaled integers, constraints or provider boundaries where they make domain semantics more honest. Lesson 2 now shows what happens when those mapping decisions evolve and SQLite cannot perform the required ALTER operation directly.

Authoritative references

SQLite type behavior spans EF, Microsoft.Data.Sqlite and the engine itself; keep all three layers visible.

• SQLite provider limitations - EF Core — unsupported modeling/query cases and migration constraints
• Data types - Microsoft.Data.Sqlite — default/alternative CLR mappings and dynamic type behavior
• SQLite function mappings - EF Core — current provider translations including decimal helper functions
• Value conversions - EF Core — converter semantics and model implications
• Datatypes in SQLite — official engine storage classes and affinity rules
• SQLite metadata - Microsoft.Data.Sqlite — PRAGMA/sqlite_master inspection patterns