You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Translate dictionary indexers on JSON columns - #3988
String-keyed dictionaries can already be mapped to JSON columns, but queries using their indexers fail to translate.
Translate indexers on Dictionary, IDictionary, and IReadOnlyDictionary through the JSON POCO traversal logic for both json and jsonb. Preserve the CLR result type and JSON mapping for containers and object values, extracting text only for scalar access, casts, and comparisons. CASE/COALESCE execute server-side when branches can share a JSON mapping, including dynamic object-valued entries. Projection branches with incompatible CLR types or custom-converter representations use EF's ordinary client composition over individually typed server projections, without a custom projection override. Dictionary infrastructure properties such as Count also use client composition rather than JSON paths. Scalar member access does not append paths to text-returning traversals, and native array methods do not operate on JSON fragments.
Preserve JSON-null materialization and coalescing semantics, including primitive collections, handle scalar/composed JsonPathExists arguments while retaining serialized-string input, and quote empty or case-insensitive NULL path keys correctly. Cast-back of already typed serialized JSON strings preserves their raw JSON representation. TypeAs follows the same JSON-aware conversion as explicit casts.
Preserve configured JSON converters for parameters and constants, including custom DOM-provider semantics for JSON null. Typed collection fragments use System.Text.Json consistently with their enclosing dictionary while retaining element metadata. Binary values, temporal scalar/array parameters (including DateOnly and TimeOnly), and registered enum parameters use serializer-compatible conversion. Native timestamptz expressions use serializer-compatible UTC suffixes independently of the session time zone; native date, timestamp, and timestamptz expressions convert infinities to CLR extrema when infinity conversion is enabled. Date-backed DateTime expressions are cast to timestamp to retain their CLR JSON representation. Registered enum and TimeSpan fragments retain JSON mappings for serializer-compatible materialization; enum casts, bitwise operations, and HasFlag, plus duration member access, arithmetic, negation, ToString, comparisons, and TimeOnly operations use ordinary client projection composition. DateTrunc does not translate JSON-backed durations as native intervals. Static Convert calls on JSON fragments, including implicitly boxed arguments, also use client composition rather than native casts. Nullable serialized-scalar HasValue checks distinguish values from JSON null and missing keys. Unsupported server-side conversion of native value-converted expressions, enum scalars and arrays, intervals, interval arrays, bytea arrays, and native date/timestamp arrays to JSON fails explicitly rather than producing incorrect comparisons. Retain direct column equality for unconverted, strongly typed jsonb literals that serialize to non-null JSON, preserving ordinary B-tree index lookups.
Contains and SelectMany over dictionary array fragments are not enabled by this change and fail before SQL generation in the referenced EF build.
Includes regression coverage for scalar and object comparisons, parameterized and missing keys, interface mappings, nested dictionaries and Count projections, converted properties and literals, JSON fragment materialization, binary JSON functions and arrays, temporal parameter precision and typed collections, non-UTC-session timestamp conversion, native date/timestamp extrema and DateOnly parameters/forced constants, date-backed DateTime finite values and extrema, unsupported native temporal array columns and constructed arrays with serialized parameter controls, registered enum projections and predicates, positive/negative day-bearing TimeSpan projections and nullable checks, unsupported dictionary collection queries, primitive-collection null comparisons and coalescing, mixed DOM/object null comparisons, boxed serialized-string compositions and cast-back, mixed-type projection shaping, native value-converted comparison rejection and client composition, indexed literal equality, and ordinary boxed-string set operations/grouping.
Keep dictionary-valued traversals out of hstore casts and retain client evaluation for non-scalar text projections so JSON deserialization is preserved. Cover nested string dictionaries, uncast projections, and JSON function composition.
Co-authored-by: Copilot App <223556219+Copilot@users.noreply.github.com>
Copilot-Session: 653e647b-2fb8-4534-878a-67994a902c38
Empty strings are valid dictionary keys, but a nested constant lookup such as e.NestedStringDictionary["Customer"][""] emits #>> '{Customer,}', an invalid PostgreSQL array literal. NpgsqlQuerySqlGenerator.GenerateJsonPath accepts the empty segment because s.All(char.IsAsciiLetterOrDigit) is true for an empty string. Require nonempty strings in that simplified-literal branch so this path uses ARRAY['Customer','']::text[], and add a nested empty-key regression.
JSON path existence incorrectly text-extracts object dictionary values
For the object-valued dictionary, JsonPathExists(e.JsonbDictionary["Name"], "$") passes ->> 'Name' to JSON_EXISTS. The seeded JSON strings become unquoted Joe/Moe, which are invalid JSON documents, so the default error behavior returns false despite those values existing. Preserve the JSON fragment for object-valued dictionary arguments, as the JSON function translator does with non-text traversals, and add a string-value regression. Keep the existing support for scalar string arguments containing serialized JSON.
Nested keys equal to NULL in any casing still pass this check. For example, ["Customer"]["NULL"] generates #>> '{Customer,NULL}', where PostgreSQL reads the second array element as SQL null rather than the literal key, so matching values are missed. Exclude case-insensitive NULL from the compact literal to use the quoted ARRAY form, and add a regression alongside the empty-key test.
Preserve JSON fragments through composed dictionary expressions
Restoring JSON extraction only for a root traversal leaves converted and composed dictionary values with the wrong JSON input. JsonPathExists(e.ReadOnlyDictionary["Age"], "$") passes CAST(... ->> 'Age' AS integer) to JSON_EXISTS, which does not accept integer input. A conditional such as JsonPathExists(e.Id == 1 ? e.JsonbDictionary["Age"] : e.JsonbDictionary["Name"], "$") instead passes text: the Name branch supplies bare Moe, not the valid JSON string "Moe". Preserve JSON fragments through scalar conversions and CASE/COALESCE operands, while retaining text extraction for intentionally serialized-string arguments. Add regressions for these forms.
Keep JSON containers and object values JSON-typed instead of relying on projection fallback. Extract text only for scalar casts and comparisons, preserving JSON materialization and null semantics through CASE and COALESCE.
Handle scalar JSON_EXISTS arguments and quote NULL path keys. Cover JSON-typed casts, composed projections, ordinary boxed-string queries, and shared POCO/array fragment projections.
Co-authored-by: Copilot App <223556219+Copilot@users.noreply.github.com>
Copilot-Session: 653e647b-2fb8-4534-878a-67994a902c38
Serialize scalar CASE and COALESCE branches as JSON values while retaining the result mapping. Compare object-valued JSON indexers through jsonb with JSON-null normalization and normal EF null compensation.
Share JSON-null handling for POCOs and typed containers without changing DOM or raw-string semantics. Add regression coverage for both json stores, mixed scalar fallbacks, missing values, and numeric/string distinction.
Co-authored-by: Copilot App <223556219+Copilot@users.noreply.github.com>
Copilot-Session: 653e647b-2fb8-4534-878a-67994a902c38
Preserve DOM root traversal, decode base64 binary values, and translate JSON-backed casts before the base visitor rejects unmapped target types. Keep JSON value kinds in uncast object comparisons.
Type JSON_EXISTS scalar constants after EF.Constant parameter inlining. Add regressions for both JSON stores and retain existing null and translation-error behavior.
Co-authored-by: Copilot App <223556219+Copilot@users.noreply.github.com>
Copilot-Session: 653e647b-2fb8-4534-878a-67994a902c38
Type constants for to_json and to_jsonb after parameter inlining. Normalize object.Equals and typed JSON parameter comparisons through the existing shared paths.
Encode binary SQL values as unwrapped base64 before JSON conversion. Cover forced constants, null/non-null parameters, equality methods, and long binary payloads on both JSON stores; update directly affected SQL baselines.
Co-authored-by: Copilot App <223556219+Copilot@users.noreply.github.com>
Copilot-Session: 653e647b-2fb8-4534-878a-67994a902c38
Removing RootElement here retains the instance's JsonDocument type and mapping. For Select(e => ((JsonDocument)e.JsonbDictionary["Name"]).RootElement), EF consequently reads a JsonDocument for a JsonElement projection and attempts an unsupported CLR conversion during query compilation. Preserve the JSON path but retarget its type and mapping to JsonElement. Add direct RootElement projection regressions for both stores; the existing GetString predicate does not exercise materialization.
A JSON-null primitive collection cannot materialize through this new mapping. For example, selecting a null entry from a JSON-mapped Dictionary<string, int[]> returns the JSON token null, not SQL NULL. FindJsonMapping selects EF's collection-to-JSON-string converter, whose collection reader requires StartArray and throws on this token. Object-to-array casts use the same mapping. Normalize JSON null before collection materialization or provide a null-aware mapping, with null-array/list projection regressions for both json and jsonb.
Parse serialized-string JSON branches instead of serializing their text. Serialize DateTime parameters through their JSON mapping and format server UTC timestamps with the serializer's suffix, preserving SQL null propagation.
Retarget JsonDocument.RootElement expressions to JsonElement for correct projection materialization. Add both-store regressions and controls for already-working null collection materialization.
Co-authored-by: Copilot App <223556219+Copilot@users.noreply.github.com>
Copilot-Session: 653e647b-2fb8-4534-878a-67994a902c38
Serialize DateOnly parameters with infinity conversions
Fixed in 8a67579: DateOnly now takes the serializer-backed parameter path, including late EF.Constant inlining. Both JSON stores have min/max parameter and forced-constant regressions.
Normalize infinity values for native date and timestamp expressions
Extended the existing infinity normalization to native DateOnly/date and DateTime/timestamp-without-time-zone expressions, using the existing infinity-conversion setting. Both-store min/max regressions cover comparisons, CASE, and COALESCE, alongside the timestamptz controls.
Cast date-backed DateTime expressions to timestamp before JSON normalization, preserving finite values and extrema. Reject unsupported native date and local timestamp arrays, including constructed arrays, while retaining serialized parameters.
Co-authored-by: Copilot App <223556219+Copilot@users.noreply.github.com>
Copilot-Session: 653e647b-2fb8-4534-878a-67994a902c38
This helper also guards SequenceEqual, which translates to equality rather than a native array function. Rejecting JSON mappings therefore removes the existing translation for predicates comparing two jsonb-mapped arrays or lists with SequenceEqual. Keep JSON operands allowed for that branch while retaining the native-array-only restriction for operations such as IndexOf, Append, and Concat. Add a regression for jsonb array equality.
Use configured JSON options for parameter serialization
This converter serializes query parameters with default options, bypassing the data source's ConfigureJsonOptions. With JsonStringEnumConverter, for example, a dictionary entry stores "Sad", but (object)EF.Parameter(mood) is serialized as 1, so an equality predicate silently misses the matching row. Parameter serialization must use the same configured options as the enclosing dictionary. Add a regression using non-default JSON options.
Infer coalesce type from non-null serialized-string operand
When a nullable serialized-string JSON expression is coalesced with an object-typed null, the unboxed operands have types string and object. This selects the object JSON mapping, so a non-null result materializes as JsonElement instead of the original serialized string. Infer the result type from the non-null operand, as VisitConditional does, and cover a nullable serialized-string projection with ?? null.
Reject native text expressions in serialized-string JSON mappings
A native text column cannot safely share the serialized-string JSON mapping. For example, Select(e => e.Id == 1 ? (object)e.CustomerJsonb : (object)e.SomeString) passes this converter check and casts SomeString to jsonb. The fixture's "Moe" value then causes an invalid-JSON error instead of returning a string. Reject native text expressions here so EF composes these projections on the client; retain the explicitly supported serialized parameters and constants.
Preserve JSON operands for SequenceEqual translation
Restored equality translation when both array/list operands are mapped to jsonb, with regression coverage for both CLR collection shapes. Native-array-only operations keep their existing restrictions.
Use configured JSON options for parameter serialization
Serializer-backed parameters now retain their CLR values and JSON parameter types, letting Npgsql apply the data source serializer instead of pre-serializing through the default-options object converter. Configured EF converters remain intact. Added a command-parameter regression using JsonStringEnumConverter for both JSON stores; the configured-options integration reproduction also passes in isolation. CASE keeps its object materialization mapping even when its first branch has a strongly typed parameter mapping.
Infer coalesce type from non-null serialized-string operand
Coalescing with an object-typed null now infers the result mapping from the non-null operand. The regression asserts the original serialized string and the null result.
Reject native text expressions in serialized-string JSON mappings
Native text no longer shares the serialized-string JSON mapping, so heterogeneous CASE projections use normal client composition rather than casting Moe to invalid JSON. Added CASE and COALESCE coverage; the valid text COALESCE path remains server-side.
Literal serialization uses default options rather than the data source's JSON configuration. With the JsonStringEnumConverter setup in the new unit test, a dictionary stores Mood.Sad as "Sad", but comparing an entry to (object)Mood.Sad serializes the constant as 1 here and misses the matching row. The EF.Parameter test avoids this converter by preserving the enum value for Npgsql serialization; it does not cover literals. Honor the configured serializer for inline and EF.Constant values too, or reject translations that cannot preserve that representation, and test both variants.
Indexer access on a coalesced dictionary still fails to translate. For example, (e.NestedDictionary["NullableCustomer"] ?? e.NestedDictionary["Customer"])["Age"] == 25 reaches this switch with a JSON-mapped COALESCE function and returns null. A JSON-mapped CASE result has the same limitation. Accept these JSON-mapped SQL expressions as traversal roots and add predicate regression tests; the existing coalesce test indexes only after materialization.
The guard checks the original expression types, so a nested boxed conditional bypasses it. For example, (e.Id == 1 ? (object)e.NestedDictionary["Customer"] : null) ?? e.Name has an object-typed left expression, but its translated conditional has a dictionary type. JSON coalescing then chooses an object mapping and materializes JsonElement values instead of the Dictionary/string branches. Check the translated, unboxed operand types before base coalescing can erase them, so this projection uses ordinary client composition. Add a regression test for this nested form.
JSON-mapped SQL expressions can now serve as traversal roots, while existing non-text traversal paths still append directly. Added predicate regressions indexing both CASE and COALESCE dictionary results for json and jsonb.
Nested boxed coalesce bypasses JSON type guard
The guard now checks translated, unboxed operand types before base coalescing can erase their mappings. The nested conditional/dictionary versus string projection uses ordinary client composition and preserves the Dictionary and string result types.
Confirmed: arbitrary CLR literals use default JSON options, while the parameter path uses the data source configuration. The same literal limitation also exists in the legacy JSON mapping on main. The scope and compatibility of rejecting the newly introduced conversions are under discussion with the operator; this finding is not yet addressed.
The reason will be displayed to describe this comment to others. Learn more.
Warning
Copilot couldn't run its full agentic review because it didn't start before the timeout. Make sure your repository has a runner available, or add a copilot-code-review.yml file specifying one with the runs-on attribute. See the docs for more details.
The static-method guard checks only the first array operand. In an ExecuteUpdate setter, Concat with a native array first and a dictionary JSON array fragment second still reaches array_cat. Since ApplyTypeMapping preserves the second operand's existing JSON mapping, this produces array_cat(integer[], jsonb) (or json), which PostgreSQL rejects. Reject a non-array mapping on Concat's second operand too, so this combination fails translation instead of SQL execution.
With the JsonStringEnumConverter configuration used in NpgsqlJsonQueryTest, dictionary writes and EF.Parameter(mood) serialize Mood.Sad as "Sad", but (object)Mood.Sad is serialized as 1 by this default serializer. EF.Constant(mood) also reaches literal generation without the configured options. Matching rows therefore depend on parameterization. Preserve configured serialization for both literal paths, or explicitly reject unsupported constant serialization, and add literal and forced-constant counterparts to the parameter test.
Primitive parameters such as int fall through to native to_jsonb(@value), bypassing converters configured through ConfigureJsonOptions. For example, a custom JsonConverter<int> that writes numbers as JSON strings stores dictionary value 25 as "25", while comparing it with (object)EF.Parameter(age) produces JSON number 25 and incorrectly finds no match. Keep primitive parameters on the configured serialization path when custom JSON converters apply, rather than assuming their PostgreSQL mapping has an equivalent JSON representation.
An object-typed parameter can contain a string, e.g. object expected = "Joe" used in e.JsonbDictionary["Name"] == EF.Parameter(expected). This branch drops ObjectJsonConverter, so Npgsql receives the string as raw JSON rather than a JSON string. Joe then fails JSON parsing; values such as 25 instead become the wrong JSON kind. Preserve string serialization for object-valued parameters without changing mappings for intentionally serialized JSON strings, and cover both json/jsonb.
Addressed the additional body findings in 9df4efe:
Concat accepts incompatible JSON mapping on second operand
The native-array Concat translator now checks the second operand too. Direct translator regressions reject mixed native/JSON operands in both orders, and ExecuteUpdate regressions assert translation failure without emitting SQL for json and jsonb.
Primitive CLR parameters now retain their CLR values with JSON parameter mappings, so Npgsql applies its configured serializer instead of native to_jsonb conversion. Added JsonConverter controls asserting the raw int and jsonb parameter mapping. Strings and byte arrays retain their existing quoting/base64 handling because Npgsql treats those CLR types as already-serialized JSON input.
The reported form already preserves its object JSON converter, including in compiled queries. Added database-backed controls for object expected = "Joe", JSON string "25" versus JSON number 25, and compiled object parameters for both stores; the values and JSON kinds remain correct, so no change to that path is needed.
This repeats the confirmed literal-policy issue discussed above. Literal rejection versus preserving the legacy default-options behavior still requires the operator decision; this commit does not disable EF.Constant or silently parameterize it.
String and byte[] parameters bypass JSON serialization here: dictionary comparisons instead use to_jsonb(@text) or base64 encoding in SQL. These operations ignore ConfigureJsonOptions converters used to save the dictionary, so the same CLR value can serialize differently and fail to match. Use serializer-aware parameter handling for these types too, while preserving raw serialized-JSON inputs, and add converter-backed comparison tests.
This literal path uses default serializer options: ObjectJsonConverter passes null options, and NpgsqlJsonTypeMapping.GenerateNonNullSqlLiteral also uses default options. With the JsonStringEnumConverter configured in Dictionary_parameter_is_not_preserialized, a literal or EF.Constant(Mood.Sad) therefore compares against JSON 1, while the stored value and EF.Parameter(mood) use "Sad". Preserve configured serialization for literals as well, and test both ordinary literals and forced constants.
String and byte[] comparisons ignore configured JSON serialization
The native to_json/base64 paths do bypass configured converters for these CLR parameter types. Mapping them directly as JSON is not a fix: Npgsql treats string and byte[] as already-serialized JSON input, so that would lose quoting/base64 semantics. Honoring custom converters here requires a serializer-backed parameter representation rather than the native SQL conversion; I have not added that plumbing pending the operator scope decision. The separate literal-policy question also remains unresolved; 6669fdb does not disable or silently parameterize EF.Constant.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
String-keyed dictionaries can already be mapped to JSON columns, but queries using their indexers fail to translate.
Translate indexers on
Dictionary,IDictionary, andIReadOnlyDictionarythrough the JSON POCO traversal logic for bothjsonandjsonb. Preserve the CLR result type and JSON mapping for containers and object values, extracting text only for scalar access, casts, and comparisons. CASE/COALESCE execute server-side when branches can share a JSON mapping, including dynamic object-valued entries. Projection branches with incompatible CLR types or custom-converter representations use EF's ordinary client composition over individually typed server projections, without a custom projection override. Dictionary infrastructure properties such asCountalso use client composition rather than JSON paths. Scalar member access does not append paths to text-returning traversals, and native array methods do not operate on JSON fragments.Preserve JSON-null materialization and coalescing semantics, including primitive collections, handle scalar/composed
JsonPathExistsarguments while retaining serialized-string input, and quote empty or case-insensitiveNULLpath keys correctly. Cast-back of already typed serialized JSON strings preserves their raw JSON representation.TypeAsfollows the same JSON-aware conversion as explicit casts.Preserve configured JSON converters for parameters and constants, including custom DOM-provider semantics for JSON null. Typed collection fragments use System.Text.Json consistently with their enclosing dictionary while retaining element metadata. Binary values, temporal scalar/array parameters (including DateOnly and TimeOnly), and registered enum parameters use serializer-compatible conversion. Native timestamptz expressions use serializer-compatible UTC suffixes independently of the session time zone; native date, timestamp, and timestamptz expressions convert infinities to CLR extrema when infinity conversion is enabled. Date-backed DateTime expressions are cast to timestamp to retain their CLR JSON representation. Registered enum and TimeSpan fragments retain JSON mappings for serializer-compatible materialization; enum casts, bitwise operations, and HasFlag, plus duration member access, arithmetic, negation, ToString, comparisons, and TimeOnly operations use ordinary client projection composition. DateTrunc does not translate JSON-backed durations as native intervals. Static Convert calls on JSON fragments, including implicitly boxed arguments, also use client composition rather than native casts. Nullable serialized-scalar HasValue checks distinguish values from JSON null and missing keys. Unsupported server-side conversion of native value-converted expressions, enum scalars and arrays, intervals, interval arrays, bytea arrays, and native date/timestamp arrays to JSON fails explicitly rather than producing incorrect comparisons. Retain direct column equality for unconverted, strongly typed
jsonbliterals that serialize to non-null JSON, preserving ordinary B-tree index lookups.ContainsandSelectManyover dictionary array fragments are not enabled by this change and fail before SQL generation in the referenced EF build.Includes regression coverage for scalar and object comparisons, parameterized and missing keys, interface mappings, nested dictionaries and Count projections, converted properties and literals, JSON fragment materialization, binary JSON functions and arrays, temporal parameter precision and typed collections, non-UTC-session timestamp conversion, native date/timestamp extrema and DateOnly parameters/forced constants, date-backed DateTime finite values and extrema, unsupported native temporal array columns and constructed arrays with serialized parameter controls, registered enum projections and predicates, positive/negative day-bearing TimeSpan projections and nullable checks, unsupported dictionary collection queries, primitive-collection null comparisons and coalescing, mixed DOM/object null comparisons, boxed serialized-string compositions and cast-back, mixed-type projection shaping, native value-converted comparison rejection and client composition, indexed literal equality, and ordinary boxed-string set operations/grouping.
Fixes #1529