Cosmos: Allow Many-to-Many relationships using a FK list without any Join Entity

[braidenstiller]

Currently in V5, you can configure a Many-to-Many relationship with an explicit join entity, or let one get created for you implicitly. Either way this will create an additional document in the database that represents this join in a many to many relationship.

When loading related data this will require atleast three queries in total. One to the entity, one to the join table and finally one to the joined entity. In read heavy applications this can get expensive fast, especially for large relationships. A better approach would be to allow storing of each entities foreign key in an array within each entity. This would allow the trade off of write optimisation (only have to write the one join table entity) for read optimisation (writing the Id directly to an array of FK on each entity in the relationship and so only having to do atleast two reads, the entity and the joined entity).

This optimisation can be found in the Cosmos Data Modelling guide here:
https://docs.microsoft.com/en-us/azure/cosmos-db/modeling-data#referencing-data

A shadow property representing the relationship as an array of foreign keys would achieve this. This could be as simple as a string tuple for entity id and partition key.

[ajcvickers]

Note from triage: putting this on the backlog; support for idiomatic many-to-many in Cosmos is something we will look at.

[roji]

Note npgsql/efcore.pg#1868 which would do the same on PostgreSQL.

[dandrejvv]

Hi.

Sorry to write this question here but is there any documentation or example code that explains how to setup and query a many-to-many relationship using EF Core for Cosmos DB?
I've managed to get it to persist the entities across three documents (as stated in the description above) where I have my two distinct entity documents and the "bridging document".

My struggle is managing to query it again from the DB. I've tried the UseLazyLoadingProxies that end up giving me an exception saying it can't translate the query and I've attempted using the Include configuration that also seems to be not supported yet. Would appreciate any assistance in this matter, either here or in another thread.

Thanks.

[ajcvickers]

Many-to-many relationships across documents are typically a pit-of-failure when using Cosmos DB--that's why traditional Include is not supported on Cosmos (#16920) and quite likely never will be. (I suggest reading the thread on #16920 for more details.) Different patterns may be reasonable to implement, which is what is tracked here for many-to-many relationships. You should vote (👍) for this issue if it's something you would use.

[dandrejvv]

@ajcvickers thanks for the reply. I've cast my vote. In the mean time I guess we'll need to manually setup our own many-to-many pattern until EF Core gives it to us out of the box.