Repository navigation
Translate the calendar factories when they read a column #191
Description
#48 (PR #190) adds DateRange.Month, Quarter, Year and IsoWeek, and the same on LocalDateRange (Quarter and Year on YearMonthRange). Called with values the query does not read, EF evaluates them in memory and inlines one daterange constant, so b.Period.Overlaps(DateRange.Month(2026, 10)) runs as SQL. Called with a column, such as DateRange.Month(b.Day), they fail translation in a Where and run in memory in a Select. docs/efcore.md#what-runs-where lists them as "not translated yet".
Proposed translation (each a [) constructor, so no canonicalization is needed):
Month(date)→daterange(date_trunc('month', d)::date, (date_trunc('month', d) + interval '1 month')::date, '[)')Quarter(date),Year(date)→ the same with'quarter'and'year'IsoWeek(date)→date_trunc('week', d), which is ISO (Monday) in PostgreSQL, plus 7 daysMonth(y, m),Quarter(y, q),Year(y)→make_date(y, m, 1)and interval arithmeticIsoWeek(weekYear, week)→to_date(weekYear || '-' || week, 'IYYY-IW'), which needs checking againstISOWeekfor week 53 of a 52-week year, since the in-memory factory throws there
What to measure first: argument validation. In memory, month 13 or week 53 of a 52-week year throws ArgumentOutOfRangeException. make_date(2026, 13, 1) raises in PostgreSQL, but to_date may roll over, which would be a silent difference. The infinity date extremes (date_trunc('month', 'infinity')) also need checking.
Done when: the column forms run as SQL, a live test compares them with the in-memory answer for every factory over a span of dates, and the row leaves "runs in memory only".
Found while implementing #48, 2026-10-18.
Scope, after the measurements (2026-10-25). The DateRange forms run as SQL. The LocalDateRange and YearMonthRange forms stay in memory: a LocalDate column at -infinity stands for -9998-01-01, and neither its month nor any year before 4713 BC has a PostgreSQL date, so the server would fail where memory returns a range. Per #408, a cell that can't match keeps the member in memory for that type. The proposal changed in three places (measurements): no to_date, a guard on the year, and [] with memory's first and last periods as constants instead of [). main: PR #447. release/10.x: #448, once #447 merges.
Activity
CaffeinatedCoder commentedon Oct 18, 2026
Plan (2026-10-18): not part of the 10.0.0 epic (#58). The column forms are additive (a translation failure becomes a working query), so this ships as a 10.x minor, 10.1.0 at the earliest, after the measurements the issue names (
to_daterollover on week 53,date_truncon the date extremes). 10.0.0 tags with the "runs in memory only" row indocs/efcore.md#what-runs-where.docs: keep the calendar factories "not translated yet", linking #191 (#…
docs: keep the calendar factories "not translated yet", linking #191 (#…
docs: the EF Core README keeps the calendar factories "not translated…
docs: the EF Core README keeps the calendar factories "not translated…
docs: resolve the merge of #399's calendar fix, committed with confli…
docs: resolve the merge of #399's calendar fix, committed with confli…
CaffeinatedCoder commentedon Oct 25, 2026
Scheduled for 10.3.0 (#408), third in priority after #405 (
Length) and #406 (Clamp). The measurements this issue names come first:to_daterollover on week 53 of a 52-week year, anddate_truncon theinfinityextremes. Then the parity list on #408. If it isn't proven in time, it moves to the next minor rather than holding the release.mainfirst, then the cherry-pick torelease/10.x.test: follow #399 and #402 in the translation-support reasons, and pl…
CaffeinatedCoder commentedon Oct 25, 2026
Measurements, 2026-10-25, on PostgreSQL 16.15 and 18.1 (
postgres:16-alpine,postgres:18-alpine). Both versions answer identically on every row.to_date('2025-53', 'IYYY-IW')(2025 has 52 ISO weeks)2025-12-29, the Monday of 2026-W01IsoWeek(2025, 53)throwsto_date('2026-54', 'IYYY-IW')2027-01-04to_date('2026-0', 'IYYY-IW')2026-01-01, not even a Mondayto_date('2026-53', 'IYYY-IW')(2026 has 53)2026-12-28[2026-12-28,2027-01-03]make_date(2026, 13, 1),make_date(2026, 0, 1),make_date(0, 1, 1)ERROR: date field value out of range(22008)make_date(10000, 1, 1),make_date(-1, 1, 1)10000-01-01,0001-01-01 BCDateRangethrows outside 1–9999date_trunc('month' | 'quarter' | 'year' | 'week', 'infinity'::date)infinity(and-infinityfor-infinity)Month(DateOnly.MaxValue)is[9999-12-01,9999-12-31]daterange(date_trunc('month', 'infinity'), … + interval '1 month', '[)')empty(both infinities)[)form fails at both extremespg_typeof(date_trunc('month', current_date))timestamp with time zonedateargument resolves to thetimestamptzoverload, which depends on the sessionTimeZonedaterange('9999-12-01', 'infinity', '[]'),daterange('-infinity', '0001-01-31', '[]')[9999-12-01,infinity],[-infinity,0001-02-01)[9999-12-01,9999-12-31]and[0001-01-01,0001-01-31]CASE WHEN 2026 BETWEEN 1 AND 9999 THEN 2026 ELSE CAST('…' || 10000 AS integer) END2026; the planner drops theELSEonce aWHENfolds to truedate_part('week', make_date(y, 12, 28))ISOWeek.GetWeeksInYearWhat this changes in the proposal
IsoWeek(weekYear, week)does not useto_date. The Monday is computed from January 4 (make_date(y, 1, 4)less itsisodowless one, plus7 * (week - 1)), and a week outside1 … date_part('week', make_date(y, 12, 28))raises.make_dateraises for a bad month and for year 0, but accepts 10000 and negative years. A year outside the type's domain raises through a cast of a message tointeger, so a bad argument fails in the database as it does in memory, with PostgreSQL's error (22P02) in place ofArgumentOutOfRangeException.[]over the first and last day, not[). A column holdinginfinityor-infinity(what Npgsql writes forDateOnly.MaxValueandMinValue) is first read as that extreme, as the column loads. A result whose first day is the domain's first, or last day the domain's last, is built with-infinityorinfinityin that place, which is the value Npgsql binds for memory's answer. PostgreSQL canonicalizes the finite]to). WithNpgsql.DisableDateTimeInfinityConversionsset, neither mapping applies, as neither does in Npgsql.date_truncgets an explicittimestamp(date_trunc('month', d::timestamp)::date), so the session time zone plays no part.IsoWeekpast the last week raises. The week holding 9999-12-27 to 9999-12-31 ends afterDateOnly.MaxValue, where memory throws; the SQL raises there too.Next: the translation on
main, then a live parity test over every factory, a span of dates and the extremes, against memory.CaffeinatedCoder commentedon Oct 25, 2026
PR #447 on
main. TheDateRangeforms run as SQL. The NodaTime forms stay in memory (scope note above).What the translation does, measured against memory on PostgreSQL 16 and 18:
daterange(first, last, '[]'). Inside the domain it isdate_trunc(unit, d::timestamp)plus'<n> <unit> -1 day'. The first and last periods are constants from memory's own factory:[-infinity,0001-01-31]and[9999-12-01,infinity]with Npgsql's infinity conversions on, and the days themselves with them off. A column holding an infinity falls into those arms.make_date, with a guard for a year outside 1 to 9999.IsoWeek(y, w)counts from January 4 and checkswagainstdate_part('week', make_date(y, 12, 28)).MaxValue, month 13, years 0, -1 and 10000, and quarters 0 and 5. The error is a cast of a readable message, for exampleinvalid input syntax for type integer: "DateRange.Month: the year 10000 is outside 1 to 9999".CASEarm no row reaches. So each message holds an argument, or the raise fires for every query.Proof:
CalendarFactoryParityTestscovers #408's parity list. It is registered as an oracle, its seed is caught, and it fails with the translation removed. Full integration runs pass on PostgreSQL 16 and 18.Merge pull request #447 from CaffeinatedCoder/feat/191-translate-cale…
Merge pull request #449 from CaffeinatedCoder/feat/448-translate-cale…