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 https://github.com/npgsql/efcore.pg/issues/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.

[amyboose]

My case is similar.

I have Npgsql provider and EF Core version 8.0.4.

My code:

using Microsoft.EntityFrameworkCore;
using Npgsql;

public class Program
{
    public static void Main(params string[] args)
    {
        InitData();

        MyContext context = new MyContext();

        User user = context.Users.First(x => x.Id == 1);
        List<Post> user1Posts = context.Posts.Where(x => user.BlogIds.Contains(x.BlogId)).ToList();
    }

    private static void InitData()
    {
        MyContext context = new MyContext();

        User user = new User()
        {
            BlogIds = new List<int> { 1, 2, 3, 4 },
            Id = 1
        };

        Blog blog1 = new Blog()
        {
            Id = 1,
            Name = "Blog1",
            Users = new List<User> { user }
        };

        Blog blog2 = new Blog()
        {
            Id = 2,
            Name = "Blog2",
            Users = new List<User> { user }
        };

        Post post1 = new Post()
        {
            Blog = blog1,
            Id = 1,
        };

        Post post2 = new Post()
        {
            Blog = blog2,
            Id = 2,
        };

        context.Set<Post>().AddRange(post1, post2);
        context.SaveChanges();
    }
}

public class User
{
    public int Id { get; set; }
    public List<Blog> Blogs { get; set; }
    public List<int> BlogIds { get; set; }
}

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<User> Users { get; set; }
}

public class Post
{
    public int Id { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<User> Users => Set<User>();
    public DbSet<Blog> Blogs => Set<Blog>();
    public DbSet<Post> Posts => Set<Post>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .HasMany(x => x.Blogs)
            .WithMany(x => x.Users)
            .UsingEntity("UserToBlog",
            l => l.HasOne(typeof(Blog)).WithMany().HasForeignKey("BlogsId").HasPrincipalKey(nameof(Blog.Id)),
            r => r.HasOne(typeof(User)).WithMany().HasForeignKey("UsersId").HasPrincipalKey(nameof(User.Id)),
            j => j.HasKey("UsersId", "BlogsId"));

        modelBuilder.Entity<User>()
            .Property(p => p.BlogIds)
            .HasColumnType("jsonb");

        modelBuilder.Entity<Post>()
            .HasOne(x => x.Blog)
            .WithMany()
            .HasForeignKey(x => x.BlogId);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        string connectionString = @"Host=localhost;Port=8431;Database=db;Username=admin;Password=adminpass";
        NpgsqlDataSourceBuilder dataSourceBuilder = new(connectionString);
        dataSourceBuilder.EnableDynamicJson();

        NpgsqlDataSource dataSource = dataSourceBuilder.Build();

        optionsBuilder
            .UseNpgsql(dataSource)
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
    }
}

What I want to get: 1) Relation User to Blog must be many to many or one to many. In my case it's many to many. 2) Foreign key to Blog is located in User entity as column BlogIds using json(jsonb) or array format. Now BlogIds property is just some unrelated to blogs property. And instead of BlogIds EF Core now use intermediate table. A solution can looks like:

modelBuilder.Entity<User>()
            .HasMany(x => x.Blogs)
            .WithMany()
            .UsingForeignKeys(x => x.BlogIds);

3) Blog entity can have or not to have navigation property to entity User. It depends on business logic. 4) UserToBlog table is redundent.

What problem it solve: 1) EF Core can fetch data without joining intermediate table. In my example (look full code above) I try to get all Posts of some user:

List<Post> user1Posts = context.Posts.Where(x => user.BlogIds.Contains(x.BlogId)).ToList();

Sql query is simple:

Executed DbCommand (49ms) [Parameters=[@__user_BlogIds_0={ '1', '2', '3', '4' } (DbType = Object)], CommandType='Text', CommandTimeout='30']
SELECT p."Id", p."BlogId"
FROM "Posts" AS p
WHERE p."BlogId" = ANY (@__user_BlogIds_0)

2) In some case many to many relationship is used just by single side. For example, I don't want to get users of Blog because of some business logic. And it means that storing BlogId in User entity allows me get a better performance.

[roji]

@braidenstiller note that this issue is about Cosmos, not PostgreSQL. You're likely looking for #30551.