Primitive collections

[roji]

This user story tracks support for primitive collections, i.e. the ability to map a property of type int[] to the database, as a JSON array (by default). Where supported, such collections are also fully queryable via the regular LINQ operators.

As a by-product of this, the translation for intParam.Contains(b => b.Property) would be changed from the current IN with constants translation to a string parameter containing an JSON array, which would be unpacked at the database and queried via the standard means. This resolves #13617, which is a long-standing, highly-voted performance issue.

Done in 8.0

• Map collections of primitive types to JSON column in relational database #29427
  • Note that in PostgreSQL, native database arrays will be used instead.
• Support querying over primitive collections #30426
• IN() list queries are not parameterized, causing increased SQL Server CPU usage #13617
• Optimized translation for indexing of JSON primitive collections #30724
• Add type mapping APIs to customize JSON value serialization/deserialization #30677
• JSON type representations and conversions to store types #30727
• Metadata and type mapping support for primitive collections #30730
• Json: add support for collection of primitive types inside JSON columns #28688
• Allow inline primitive collections with parameters, translating to VALUES #30732
• Support primitive collections in the compiled model #31489
• EF primitive collection - nested string comparison operations #31030
• Using "nvarchar" for explicit store type of primitive collection throws #30919
• Most cases of projecting a primitive collection don't work #30922
• Parameter for collection of enum values contains ints even when correlated column has strings #30921
• Support arbitrary expressions in inline collections (translate to VALUES) #30734

Done in 9.0

• Use EF8-style primitive collections in the Cosmos provider #25364
• Cosmos: Support queries into primitive collections #25765
  • Cosmos: translate Length/Count on collections #25700
  • Cosmos: translate indexing into collection  #25701
  • Cosmos: Translate composition over embedded non-primitive collection #16926
• [Cosmos] Support collections of scalar types #4179
• Use EF8-style primitive collections in the Cosmos provider #25364

Backlog

• Support mapping (and querying) of geometry collections #30630
• Improve type mapping inference for primitive collections #31109
• Technical debt: always produce inline/parameter query roots in preprocessing #31069
• Handle entity Contains via queryable constant/parameter parameter roots #30712
  • Change Translate methods on QueryableMethodTranslatingEV to accept untranslated source #30711
• Query support for nested collections of primitive types #30713
• Add support for collections of primitive types as separate table in relational databases #25163
• Translate SequenceEquals #30786
• Add a way to configure the concrete type for a primitive collection. #31287
• JSON: Allow weakly-typed mapping via Dictionary #29825

Related issues

• Support Jagged and Multidimensional Arrays (Cosmos DB) #26824
• Cosmos: allow value converters for collections of primitive types #25343

[vchirikov]

@roji I'm sorry to bother you, I found that builder.OwnsMany(x => x.ListOfStructsProperty, x => x.ToJson()); doesn't work on net8.0 and I didn't find the issue to track. Should I create it or the issue is already here somewhere?

[roji]

@AndriySvyryd do we have an issue already tracking this? I found #28861 but that seems to be something else (am still not sure exactly what though 😅 )

In any case, we're likely to work on mapping JSON through complex types (as opposed to owned entities), and should make that work there - JSON mapping via owned entity types is likely to be de-prioritized.

[AndriySvyryd]

@vchirikov Mapping owned collections to JSON is generally supported. Can you open a new issue that shows the types being mapped, the error that you get and how you expect the JSON to look?

Oh, I see now. We only allow structs to be mapped as complex types. So as @roji mentioned, be on the lookout for #31252 and #31411

[zN3utr4l]

Do you have any suggestions for implementing a List<List< int>>? since it doesn't work with OwnsMany either.

#33913