Many-to-many using a JSON array column

[ajcvickers]

Similar to #23523 for Cosmos.

[AndriySvyryd]

The array could contain the FK values for the related entities, see npgsql/efcore.pg#1868

[roji]

@AndriySvyryd yeah. I closed npgsql/efcore.pg#1868 as a dup of this.

Modeling-wise, I guess that instead of the user defining a join table, they'd define a join column which must be a (queryable) collection. Each side of the navigation would need to do this in order for the navigation to be bi-directional. No skip navigations would be involved.

Here's how this would work query-wise:

DROP TABLE IF EXISTS Blogs;
DROP TABLE IF EXISTS Posts;
CREATE TABLE Blogs (Id INT IDENTITY PRIMARY KEY, PostIds NVARCHAR(MAX));
CREATE TABLE Posts (Id INT IDENTITY PRIMARY KEY, Name NVARCHAR(MAX));
INSERT INTO Posts (Name) VALUES ('Post1'), ('Post2');
INSERT INTO Blogs (PostIds) VALUES ('[1, 2]');

SELECT * FROM Blogs AS b
CROSS APPLY OpenJson(b.PostIds) WITH (PostId int '$') AS pids
INNER JOIN Posts AS p ON p.Id = PostId;

[AndriySvyryd]

No skip navigations would be involved.

I think it would still have to be implemented using skip navigations, as that's the only way of having an FK on each side, we just need to allow the FKs to be defined on different types and to use collection properties.