Native JSON support for EFCore

[weitzhandler]

Related:
dotnet/efcore#4021
dotnet/efcore#2282

In my vision, there should be an attribute let's say SqlJsonAttribute or whatever, that when we use it on an entity property, a string-based (e.g. nvarchar) column is created for it in the DB, and a HasConversion is added to the DbContext for that property instructing EF to transform its contents into JSON (whether it's a complex object, array or whatever), using the JSON serializer registered with the IDbContextServices/IDatabaseProviderServices fail-safing to JSON.NET.

A middleware will be injected to the EF runtime that intercepts the queries and rewrites all the parts that concern those jsonized properties into OPENJSON SQL queries, so that from the point of view of the layer using EF, no special attention has to paid when querying those properties as far as it concerns LINQ to SQL support.

[tuespetre]

I like it a lot.

OPENJSON has been on my radar since writing this test case:

Impatient/test/Impatient.Tests/QueryTests.cs

Lines 4276 to 4301 in b1c10f5

	[TestMethod]
	public void Weird_Query()
	{
	var query = from q in (from x in impatient.CreateQuery<MyClass1>(MyClass1QueryExpression)
	from zs in (from y in impatient.CreateQuery<MyClass2>(MyClass2QueryExpression)
	select impatient.CreateQuery<MyClass2>(MyClass2QueryExpression).Where(z => z.Prop2 == x.Prop2))
	select new { x, zs }).Take(10)
	from z in q.zs
	select new { q.x, z };
	query.ToList();
	Assert.AreEqual(
	@"SELECT TOP (10) [x].[Prop1] AS [x.Prop1], [x].[Prop2] AS [x.Prop2], [zs].[$c] AS [zs]
	FROM [dbo].[MyClass1] AS [x]
	CROSS APPLY (
	SELECT (
	SELECT [z].[Prop1] AS [Prop1], [z].[Prop2] AS [Prop2]
	FROM [dbo].[MyClass2] AS [z]
	WHERE [z].[Prop2] = [x].[Prop2]
	FOR JSON PATH, INCLUDE_NULL_VALUES
	) AS [$c]
	FROM [dbo].[MyClass2] AS [y]
	) AS [zs]",
	SqlLog);
	}

I imagine implementing that will lay most of the needed foundation for other kinds of JSON usage.

[weitzhandler]

I've updated my question, please have a look!
Thank you for this awesome work!

[tuespetre]

I've got a proof-of-concept in the branch json-party. Here's a test case using OPENJSON, JSON_VALUE, and JSON_QUERY:

Impatient/test/Impatient.Tests/QueryTests.cs

Lines 4617 to 4643 in 0cc45a8

	[TestMethod]
	public void Query_JsonArray_InJsonObjectProperty_Crazy()
	{
	var m1js = impatient.CreateQuery<MyClass1Json>(MyClass1JsonQueryExpression);
	var query = from x in m1js
	from y in x.Prop1.Test.Select(t => new { t, x })
	from z in y.x.Prop1.Test
	where z.Prop == y.t.Prop
	select new { x, y, z, x.Prop1.Test };
	var results = query.ToList();
	Assert.AreEqual(
	@"SELECT [x].[Prop1] AS [x.Prop1], [x].[Prop2] AS [x.Prop2], [y].[t] AS [y.t], [y].[x.Prop1] AS [y.x.Prop1], [y].[x.Prop2] AS [y.x.Prop2], [z].[value] AS [z], JSON_QUERY([x].[Prop1], N'$.Test') AS [Test]
	FROM [dbo].[MyClass1Json] AS [x]
	CROSS APPLY (
	SELECT [j].[value] AS [t], [x].[Prop1] AS [x.Prop1], [x].[Prop2] AS [x.Prop2]
	FROM OPENJSON([x].[Prop1], N'$.Test') AS [j]
	) AS [y]
	CROSS APPLY (
	SELECT [j_0].[value]
	FROM OPENJSON([y].[x.Prop1], N'$.Test') AS [j_0]
	) AS [z]
	WHERE JSON_VALUE([z].[value], N'$.Prop') = JSON_VALUE([y].[t], N'$.Prop')",
	SqlLog);
	}

While not ridiculously dynamic in the sense of using IDictionary<string, object> or anything like that, at least the complex sub-properties could be iterated upon rapidly without necessitating migrations or schema changes. I'm going to need to let it stew for a while and think about possible gotchas; at first thought, the trickiest areas are going to be:

• guaranteeing that the originating projection can always be traced back from a given JSON function expression in order to preserve special expressions, for instance, EntityMaterializationExpressions which need to interact with change tracking
• making appropriate decisions in regards to the JsonSerializerSettings and whatnot to use on a column-by-column basis. Some columns may need to be camelCase, some snake_case, some need to use a specific ContractResolver, etc. and while having support at all is a good start, those options will be important to consider down the line.

[tuespetre]

I've gone ahead and merged what I have so far into master. Short of offering a means of providing a custom JsonSerializerSettings I don't know that there's much else to do besides handle issues as they come up.

Now we only handle the query side of things, not anything else in EF (for now) so it looks like maybe EF Core 2.1 with its ValueConverters will be the ticket for this to actually get some use.

[weitzhandler]

Thanks for your awesome work!

[tuespetre]

Oops! Looks like I forgot that with JSON, array indexing can be a translatable thing.

https://aspnetcore.slack.com/archives/C0E1PN874/p1525166978000116

Right now it should be working although it will run on the client side. I’m reopening this until I can get some tests and implementation for translating that to server side in there.

[tuespetre]

I’ve got something working for both array indexing (with constant and variable index arguments) as well as other indexing expressions. I’m still trying to decide whether or not to limit the ‘other’ indexing expressions to string keys on dictionary types, and I’m still trying use a little bit of combinatorics to come up with a suitable test suite for the functionality.