Pomelo.entityframeworkcore.mysql: Implement full/extended JSON support

[lauxjpn]

As the last major feature before 5.0, we are going to implement proper JSON support, where either JsonDocument, JsonElement, System.String or custom POCOs can be used in conjunction with the json column type.

We will support both stacks, System.Text.Json and Newtonsoft.Json.

This issue is related to #693 and #598.

[mguinness]

Thanks Laurents, appreciate your efforts on this. @roji Since you've already implemented JSON support for PostgreSQL do you have any pointers and has any design work happened for SQL Server?

[roji]

I had hoped we'd be able to deliver SQL Server support for 5.0 (https://github.com/dotnet/efcore/issues/4021), but that's unfortunately not going to happen - too much going on... I definitely think it's going to be a priority item for 6.0.

@lauxjpn it would be great to know more about your plans for the MySQL provider, when those plans become clear :) Ideally we'd manage to align support at least to some extent across providers. If you're curious to see what I did for PostgreSQL, here's a post on that and here are the docs. I decided to only support System.Text.Json for now (among other things because Newtonsoft.Json requires adding the dependency to the provider). I implemented both strongly-typed POCO support and weakly-typed DOM support, including some nice query features (i.e. get all documents where a certain property inside the JSON equals something). If you want to implement that kind of thing I'd be happy to help!

[lauxjpn]

@roji Thanks for the post, I wasn't aware of that!
I already took ... I mean "borrowed" your work as the baseline for Pomelo, that I want to support first as much as possible (I am about half way done at the moment). After that, it might be time to take a look at what interesting stuff MySQL and MariaDB could bring to the table (probably at least a couple of beers).
As for the two stack support, the current plan is to have two stack specific packages (e.g. Pomelo.EntityFrameworkCore.MySql.Json.Newtonsoft), and either an additional package for the common (stack independent) JSON support (like Pomelo.EntityFrameworkCore.MySql.Json), or just integrated common JSON support in the main provider package that only works when a provider specific package has been added (for the needed serialization).

[roji]

Definitely a couple of beers :) That's starting to be even possible...

The problem with external packages here (plugins), is that advanced pluggability into the query pipeline is limited. You can definitely have method and member translators, but other stuff currently isn't possible (at least not in an easy way). For example, indexing into an array (BinaryExpression with NodeType ArrayIndex) can only be translated in the provider's SqlTranslatingExpressionVisitor, which is definitely something you need for full JSON translation. We've had conversation on better translation extensibility for plugins, but I don't think we're going to be able to bring that for 5.0.

/cc @smitpatel @maumar @ajcvickers

[lauxjpn]

Yeah, it might be easier to keep the common support in the main provider project and only have the stack specific stuff, basically an adapter for the stack's serializer (and some types), in their individual packages.
That way, we won't need to implement our own plugin extensibility points (though this is possible of course if needed, so no sweat about not having more extensibility support out-of-the-box in EF Core yet).

[roji]

Yeah. Keep in mind that System.Text.Json is in-the-box since .NET Core 3.1, so no real reason to separate it out to a plugin - that's one of the reasons I implemented things this way. If you really want to do a Newtonsoft.Json plugin and support expression beyond method/member access, then yeah, it's always possible to hack a provider-specific plugin mechanism (I had one back in... 2.0?), but better to avoid that :)

[lauxjpn]

Keep in mind that System.Text.Json is in-the-box since .NET Core 3.1 [...]

Ah, I wasn't aware of that. In that case it might not be needed to keep the System.Text.Json stack support in its own package. I will take a look at how things shape while they are being implemented.

I still think, that Newtonsoft.Json support is important though, since we would be supporting types like JsonDocument and JsonElement, that can be directly used in further work in the application, which might need (or want) to use Newtonsoft.Json due to the still existing limitations of the Microsoft stack.
Also, people might already be using Newtonsoft.Json in their apps for years, and have been using their own (or our current but quite limited) Json solution for persistence. Though I have no idea if those numbers are significant.

[roji]

There's definitely people who want to use Newtonsoft.Json, for sure. I just preferred to implement support for one of them as a start - fully implementing support for two JSON packages is a lot of work. To be honest, although a few people have asked for Newtonsoft.Json, it hasn't been that many.

[lauxjpn]

It is basically what I do at the moment. I get the baseline code to work with System.Text.Json. Then I will abstract away the stack dependent stuff and finally decide, how to package everything.

I am maintaining some real world projects, that use JSON and MySQL and depend heavily on the PreserveReferencesHandling setting (and others). So I have a personal interest in making sure, that Newtonsoft.Json is supported.

And of course it gives the implementation a slight spin, which makes it a bit more interesting, then just converting the Npgsql implementation to Pomelo.

[roji]

Sure thing - I'll be interested to see what you come up with for sure.

[roji]

If translating array indexing thing becomes a blocker for you (for a plugin), let me know and I can try to see how you can work around it.

[lauxjpn]

It currently looks like we will be releasing JSON stack specific packages after all.

Even with System.Text.Json being part of the .NET Core 3.1 release, our current version of Pomelo is backward compatible to .NET Core 2.0 and .NET Framework 4.6.1+.

I consider it likely that the majority of users are not going to use the JSON support at all, and the users who depend heavily on JSON support are likely to use Newtonsoft.Json.

We will therefore make sure, that nobody needs to load a package, that they don't use and (in case they are already using Newtonsoft.Json in other parts of their program) that might even contain the same class names as the package they are actually using, which could result in making the development process a bit more inconvenient.

[mguinness]

It hadn't occurred to me that JSON support would be extended to 2.x - based on your comment would that mean using the latest 3.x provider?

Looking forward, do you anticipate that for the EF Core 5 release JSON support will be integrated as opposed to needing a separate package?

[lauxjpn]

It hadn't occurred to me that JSON support would be extended to 2.x - based on your comment would that mean using the latest 3.x provider?

Using Pomelo 3.2.x+ and EF Core 3.1.x+ will be mandatory for JSON support. But because Pomelo 3.1/3.2 and EF Core 3.1 are .NET Standard 2.0 compliant, those package versions can be used together with .NET Core 2.x+ and .NET Framework 4.6.1+.

Looking forward, do you anticipate that for the EF Core 5 release JSON support will be integrated as opposed to needing a separate package?

No, it will stay to be available as separate packages. Technically, the basic JSON support is part of our main package, but since a (de)serializer is needed in most cases, which depends on either the Newtonsoft.Json or System.Text.Json reference, it can only be enabled if one of those packages has been referenced.

Theoretically, a (de)serializer is not needed for the case, that a System.String property is used in conjunction with the json store type, where the property just contains the raw JSON text. This might work in the end without the need to reference a JSON specific package.

[lauxjpn]

The question for 5.0 is now, what we do with JsonObject<T>?

---

We could just drop it and force everybody to use the new support.

---

We could also keep JsonObject<T> around for legacy JSON support for 5.0. In that case, we should move it into its own package Pomelo.EntityFrameworkCore.MySql.Json.Legacy, where we can either combine the actual type (currently in Pomelo.JsonObject) with a type mapping source plugin (the current type mapping code is in Pomelo.EntityFrameworkCore.MySql), or keep JsonObject<T> in Pomelo.JsonObject and just move the type mapping code to Pomelo.EntityFrameworkCore.MySql.Json.Legacy. As a result, we could remove the dependency to the current Pomelo.JsonObject package from Pomelo.EntityFrameworkCore.MySql.

Changing the package name to bring it in line with the other JSON support packages would have the side effect, that users might directly apply one of the new JSON support packages when upgrading and only use the legacy package if they are motivated enough to dig it out (probably if their investment into JsonObject<T> is quite substantial).

For 6.0, we would then drop support for it for good.

---

Since the JsonObject<T> layer was already very thin, I would lean to just dropping support for it in 5.0 and handle the aftermath in our issue section.

[mguinness]

I would also lean to drop support in 5.0, we could give a heads up in EF Core 5 Preview support issue.

@yukozh Will this affect any of your projects like Pomelo.Data.MyCat or will that no longer be updated?

[lauxjpn]

@yukozh Will this affect any of your projects like Pomelo.Data.MyCat or will that no longer be updated?

I think most of those projects fall under #1148.

[lauxjpn]

I move the announced release date for version 3.2.0 from 2020-09-13 to 2020-09-15, so we can ship it together with the new JSON change tracking support (see #1140 and #1159).

[connywesth]

2020-08-15 is still almost a Month ago, caan you at least move the date forward to 2020-09-15, that's tomorrow?

[lauxjpn]

@connywesth Good point :)
It already was stated correctly on our main page, just not in my post above (corrected now).

[sonuame]

Just now I broke one project of a client by upgrading the ef core to 5. scaffolded the DB, modified all the pluralizations and bammm... Pomelo freaked out... at runtime...

[lauxjpn]

@sonuame We currently don't have a version released, that is compatible with EF Core 5.0 (see Compatibility: Dependencies and Schedule and Roadmap).

However, there should be a first version in the nightly builds around Monday or Tuesday and a first official alpha available by the end of next week (tracked by #1088).