JSON, Date/Time, Decimal, Collation, Foreign Key, and Function Translation Differences

Learning outcomes

1. The problem: “LINQ translated” still does not mean “same SQL semantics everywhere”

Chapter 08–10 taught the EF translation pipeline and Chapter 19 showed SQL Server-specific SQL. SQLite translates many of the same LINQ expressions, but its JSON functions, text collations, temporal functions, decimal helpers, foreign-key activation and unsupported operators differ. This lesson uses the existing ServiceHub DispatchProfile JSON mapping and CreatedUtc shadow property so provider differences stay connected to the course model.

2. EF Core 10 complex JSON mapping remains a relational provider feature, but SQL is provider-specific

Chapter 06 mapped WorkOrder.DispatchProfile to one JSON column. On SQLite, EF translates member access through SQLite JSON functions rather than SQL Server JSON_VALUE. Query/update the object through LINQ; inspect the actual provider SQL every time a path or update shape matters.

builder.ComplexProperty(x => x.DispatchProfile, json =>{    json.ToJson("dispatch_profile");    json.ComplexProperty(x => x.Contact);    json.PrimitiveCollection(x => x.RequiredTools);});

var gateVisits = await db.WorkOrders    .Where(w => w.DispatchProfile.AccessInstructions.Contains("gate"))    .Select(w => new    {        w.WorkOrderNumber,        ContactName = w.DispatchProfile.Contact.Name    })    .ToListAsync(ct);

SELECT "w"."work_order_number",       json_extract("w"."dispatch_profile", '$.Contact.Name')FROM "work_orders" AS "w"WHERE instr(    json_extract("w"."dispatch_profile", '$.AccessInstructions'),    'gate') > 0;

3. Partial JSON updates can become json_set, but verify the changed path and concurrency predicate

EF Core 10 can update individual properties inside mapped JSON rather than rewriting an entire document in important cases. For ServiceHub, the update must also retain the application-managed revision predicate when it is performed through tracked SaveChanges.

UPDATE "work_orders"SET "dispatch_profile" = json_set(    "dispatch_profile",    '$.Contact.Phone',    json_extract(@p0, '$[0]')),    "revision" = @p1WHERE "work_order_id" = @id  AND "revision" = @original_revisionRETURNING 1;

Do not copy this exact SQL into application code. It is evidence for one provider/version/query shape. SQL Server/PostgreSQL use different JSON types/functions and may have different indexing/update plans.

4. DateTime has many SQLite translations; DateTimeOffset remains a different story

The current SQLite provider maps many DateTime operations to datetime, strftime and julianday. EF Core 10 also maps DateOnly.DayNumber. Those mappings are why the course’s UTC DateTime CreatedUtc shadow property is a better mandatory SQLite filtering/ordering primitive than DateTimeOffset OpenedUtc.

var cutoff = DateTime.UtcNow.AddHours(-4);var stale = await db.WorkOrders    .Where(w => EF.Property<DateTime>(w, "CreatedUtc") < cutoff)    .OrderBy(w => EF.Property<DateTime>(w, "CreatedUtc"))    .Take(100)    .ToListAsync(ct);

WHERE "w"."created_utc" < @__cutoffORDER BY "w"."created_utc"-- examples of provider functions for DateTime members:datetime('now')strftime('%Y', "created_utc")julianday("created_utc")

If offset preservation is a business requirement, keep OpenedUtc for round-trip/display semantics and introduce a separately queryable normalized instant rather than forcing one representation to solve every problem.

5. Decimal translation uses EF helper functions, not native fixed-precision SQL arithmetic

EF Core’s SQLite provider registers functions prefixed ef_ for decimal math/comparison and aggregate cases. For example, comparisons can use ef_compare, sums can use ef_sum, and arithmetic can use ef_add/ef_multiply. This is a provider feature layered on SQLite’s primitive storage model.

var probes = db.Set<SqliteTypeProbe>();var expensive = await probes    .Where(x => x.EstimatedCost >= minimum)    .Select(x => new { x.Id, x.EstimatedCost })    .ToListAsync(ct);var total = await probes    .SumAsync(x => x.EstimatedCost, ct);

WHERE ef_compare("s"."estimated_cost", @__minimum) >= 0SELECT ef_sum("s"."estimated_cost") FROM "sqlite_type_probes" AS "s";

Still verify ordering/index behavior and data distribution. A helper function can make an expression translatable while changing sargability/cost compared with a native numeric column.

6. SQLite collation is not .NET StringComparison and NOCASE is ASCII-only by default

SQLite ships BINARY, NOCASE and RTRIM collations. Built-in NOCASE is case-insensitive only for ASCII A–Z. The LIKE operator does not honor collations and by default has similar ASCII-only case-insensitive behavior. Therefore a Unicode customer-name search can differ from .NET and SQL Server.

builder.Property(x => x.CustomerName)    .UseCollation("NOCASE");var exact = await db.WorkOrders    .Where(w => EF.Functions.Collate(w.CustomerName, "BINARY") == name)    .ToListAsync(ct);

await db.Database.OpenConnectionAsync(ct);var sqlite = (SqliteConnection)db.Database.GetDbConnection();sqlite.CreateCollation(    "SERVICEHUB_NOCASE",    (left, right) => string.Compare(        left, right,        StringComparison.OrdinalIgnoreCase));

7. Foreign-key clauses and active foreign-key enforcement are separate facts

SQLite supports foreign keys, but enforcement is connection/runtime state. Microsoft.Data.Sqlite can send PRAGMA foreign_keys = 1 immediately after opening when Foreign Keys=True. The bundled native library may already be compiled with foreign keys enabled, but the course lab sets the connection behavior explicitly and verifies it.

var cs = new SqliteConnectionStringBuilder{    DataSource = "servicehub-sqlite-deepdive.db",    ForeignKeys = true}.ToString();

PRAGMA foreign_keys;PRAGMA foreign_key_list('work_order_notes');PRAGMA foreign_key_check;

A green EF relationship test is not enough if another application opens the same database with different connection settings. Test the runtime configuration used by every writer.

8. Provider function mappings are versioned API behavior

The EF Core 10 SQLite provider currently translates many .NET members: string.Contains to instr, Regex.IsMatch to REGEXP, DateTime.UtcNow to datetime('now'), trigonometric functions, binary hex/substr helpers, and more. Some mappings were added in specific EF versions; EF Core 11 adds additional ordered aggregate forms that are not part of this course baseline.

var query = db.WorkOrders    .Where(w => w.CustomerName.Contains(term))    .Where(w => EF.Property<DateTime>(w, "CreatedUtc").Year == year)    .Select(w => new { w.Id, w.WorkOrderNumber });Console.WriteLine(query.ToQueryString());

Capture SQL in provider integration tests when a translation is business-critical. A future provider upgrade can change SQL shape without changing your LINQ source.

9. Failure case: a provider-specific assumption passes SQLite and fails production—or vice versa

A developer tests Unicode case-insensitive matching with ASCII sample names and concludes the query is portable. Production includes non-ASCII names under a SQL Server collation and results differ. Another team orders DateTimeOffset under SQL Server successfully and expects the same SQLite translation.

Repair: define the required text/time semantics in acceptance tests, then execute those tests against every supported provider. SQLite-only success proves SQLite behavior—not abstract relational correctness.

10. Mandatory lab: build a provider behavior notebook

1. Record EF/SQLite/Microsoft.Data.Sqlite versions, native sqlite_version(), journal mode and PRAGMA foreign_keys.
2. Run the DispatchProfile nested JSON query and one tracked phone update; capture json_extract/json_set SQL.
3. Filter/order by shadow CreatedUtc and contrast it with the OpenedUtc DateTimeOffset boundary.
4. Run one decimal comparison and aggregate, capturing any ef_* functions in generated SQL.
5. Compare BINARY and NOCASE with ASCII and non-ASCII sample names; record the difference.
6. Temporarily open a raw connection with foreign-key enforcement disabled only in the disposable lab, demonstrate why runtime state matters, then restore Foreign Keys=True and run foreign_key_check.
7. Store SQL/results as test evidence; do not alter global production collations or pragmas to complete the exercise.

11. Production judgment and bridge

SQLite’s EF provider is capable, but every success has three layers: EF translation, Microsoft.Data.Sqlite driver behavior, and SQLite engine semantics. JSON, decimal helper functions and DateTime translations can be excellent tools when measured. Collation and foreign-key settings are runtime contracts, and provider-specific tests are mandatory for cross-provider products. Lesson 5 closes the chapter by deciding when SQLite itself is the right production database and when using it as a stand-in for another provider creates false confidence.

Authoritative references

Treat translation tables and connection settings as versioned provider behavior, not eternal ORM guarantees.

• SQLite function mappings - EF Core — current .NET-to-SQLite translations and ef_* helpers
• Complex types - EF Core — EF Core 10 relational JSON mapping for complex values
• SQLite provider limitations - EF Core — DateTimeOffset/decimal/TimeSpan/ulong and modeling limits
• Collation - Microsoft.Data.Sqlite — BINARY/NOCASE/RTRIM, custom collations and LIKE behavior
• Connection strings - Microsoft.Data.Sqlite — Foreign Keys, timeout, pooling and cache options
• Foreign key support - SQLite — engine foreign-key configuration and semantics