Add relational JSON mapping support for complex types

[Timovzl]

If the property being mapped here is a collection [of value objects], perhaps structural equality should be preferred on the collection. The value objects inside can simply override Equals() et al, but that is not as feasible for the collection holding them.

[roji]

@Timovzl structural equality is definitely planned for the collections, just as it already works for the complex types themselves. However, in change tracking EF doesn't compare complex types to one another directly (e.g. via their Equals), but rather compares the properties contained in the complex types (since it needs to know which properties changed).

[aradalvand]

Already the second most upvoted open issue in the repo in just over 4 months 😅 People seem to really want this!

[roji]

@atrauzzi I don't follow all of the above, but EF's implementation here will definitely not simply be System.Text.Json's polymorphism capabilities (nor will it magically recognize your own hand-rolled [JsonPolymorph]). As explained in #31250, this will very likely end up being EF modeling the inheritance hierarchy and discriminator (just like it already does with TPH), and implementing JSON serialization/deserialization via the low-level Utf8JsonReader/Rwriter (just like we already do with owned entity JSON support).

In any case, this really doesn't belong on this issue, but rather on #31250 - I'll hide these comments to keep things clean.

[inetdevgit]

Shouldn't be stored in JSON because complex type only meant for the app designed for it. DB itself should considered accessed by other app as well such as SSMS. But I do prefer complex type in my App while keeping flat for SSMS or others.

[roji]

@inetdevgit I'm not sure what you mean in your comment... This issue is about allowing complex types to be mapped to JSON, but by default, non-collection complex types will still be "flattened" (i.e. no mapped as JSON). For collections of complex types, we don't plan to support any other mapping form other than JSON.

Regardless, storing data as JSON columns is a standard and supported SQL Server feature, and not some EF-specific thing; needing to use SSMS (or other apps) should not be a reason to avoid JSON.

[m-dehghani]

Can we have an indexing and fast searching on these Json column?

[roji]

@m-dehghani EF doesn't currently set indexing for JSON columns (#28605 tracks that), but you can add a computed column and create an index over that (see these docs for more information).

Note that for SQL Server, a new JSON type is being introduced, with dedicated indexing capabilities probably coming too. When those come out we'll see how we can support them in EF (follow #28605 for that).