NET7: InvalidOperationException when mapping to the same jsonb column from different entities

[kev-andrews]

Class A: BaseClass

[Column(nameof(Configuration), TypeName = "jsonb")]
public ConfigurationForClassA Configuration { get; set; } = new();

Class B: BaseClass

[Column(nameof(Configuration), TypeName = "jsonb")]
public ConfigurationForClassB Configuration { get; set; } = new();

This worked in .NET 5&6 (with Ef Core 5/6) by re-using the column in the baseclass table. Breaks after upgrading to .NET 7 and EF7.

are both mapped to column 'Configuration' in 'BaseClassTable', but are configured to use differing provider types

I would assume that there is some conflict with the new json support in EF7, maybe there is some requirement to mark classes used in json columns in some way? e.g. with an [Owned] attribute?

[roji]

The new ToJson support in EF7 isn't currently implemented in EFCore.PG, so you can't use [Owned] (we intend to converge the two JSON support strategies for EF 8.0). However, I'm guessing that some tighter model validation was added in EF 7.0 around this, which causes your model to break.

To be honest, I suspect that if this worked previously, it's somewhat by accident... @maumar @AndriySvyryd any thoughts here?

[AndriySvyryd]

This validation is not related to the JSON work. In general, the update pipeline now assumes the provider CLR type is the same for all properties sharing a column.

@roji Is there a default converter for JSON type mapping like to string or JsonDocument?

[roji]

@AndriySvyryd in the PG provider, the JSON support is implemented at the lower-level ADO.NET provider; you can create an NpgsqlParameter, set its NpgsqlDbType to Json (or Jsonb), and then whatever value you set there will be converted by the ADO.NET driver to JSON (using System.Text.Json). So there's no value conversion happening at the EF level - think of it as an EF type mapping that can accept any CLR type.

We just had a quick discussion with @ajcvickers and @maumar about allowing something similar even in other providers, with the new 7.0 ToJson support, i.e. have a hierarchy with two different CLR types mapped to the same JSON column.

[AndriySvyryd]

Ok, we could change the validation to only check whether the provider types match if there's an FK/AK/PK or unique index defined on the column in these cases the value needs to be converted on the EF side for it to be able to sort the commands correctly.

[kev-andrews]

Is there any way to work around this currently? Are there flags to disable the validation or are we basically stuck on efcore6 for now?

[roji]

I'd recommend not mapping the two properties to the same column; this means you'd have two columns instead of one, where only one would be populated for each type in the hierarchy. You can even add a computed column which exposes the two columns via the same name, so you end up with a schema that looks almost like it does today.

[kev-andrews]

Ok, i will look into it, might be a hassle as the application i am trying to update is live, has 500k rows in a TPH table with 15 derived classes which only differ in 2-3 Columns as well as the json "Configuration" Column. I assume that i would have to write a script that copies values to the new configuration columns based on the discriminator manually as part of a migration.

[roji]

Good luck...

Another approach would be to unify the CLR types - just have a single CLR type, where some of the properties would be null for one hierarchy type but not for another. This should make it possible to make things work without any database change (i.e. only code changes).

[kev-andrews]

@AndriySvyryd Any chance for this change happening any time soon? Before we start a huge refactor ;)

Ok, we could change the validation to only check whether the provider types match if there's an FK/AK/PK or unique index defined on the column in these cases the value needs to be converted on the EF side for it to be able to sort the commands correctly.

[roji]

Closing this as dotnet/efcore#29859 was opened - less continue the conversation there.