Access transparent EF managed join entity (many to many relationship) without class/DbSet

I have taken the code first approach (EF Core 7.0). I have not explicitly defined the join entity using a class. Now I want to access the join entity and delete or add some rows there, which I am not able to do.

Model reduced to the essentials. In reality, there are many columns in A and B.

public class A
{
    public int Id { get; set; }
    public List<B> Bs { get; } = new();
}

public class B
{
    public int Id { get; set; }
    public List<A> As { get; } = new();
}

Let's say in the table there are the following data:

Table A:
1
2
3
4
5
6


Table B:
1
2
3

EF managed join entity Table AB:
1 1
1 2
1 3

I have two lists. Add and Remove. The IDs should be added or removed from the joined entity.

A ID: 1

List ToAdd: 4, 5
List ToRemove: 2,3

At the end the table should look like this

EF managed join entity Table AB:
    1 1
    1 4
    1 5

How do I achieve the desired result very efficiently? I would like to access directly the entity managed by EF Core, but this is not possible because no class exists and no DbSet.

Any help would be appreciated. Thanks a lot!

You actually can define the join entity yourself and move the navigational properties into this entity. It will yield the same result on the database as what you are already doing, but you will also have the join entity which you can use with LINQ. Here is how you could do this:

public class A
{
    public int Id { get; set; }
}

public class B
{
    public int Id { get; set; }
}

public class AandB
{
    public List<A> As { get; } = new();
    public List<B> Bs { get; } = new();
}

I'm not sure I follow your example. If you have sets of BIds that you want to add and remove respectively, you can do so with A's navigation property to Bs directly. The catch that often trips up developers new to EF is to do so you need to ensure you first eager load the Bs collection. For example if I have two collections: BsToAdd and BsToRemove:

A a = _context.As
    .Include(a => a.Bs) // <- Make sure the Bs are loaded and available.
    .Single(a => a.Id == aId);

foreach(var b in BsToRemove)
    a.Bs.Remove(b);

foreach(var b in BsToAdd)
    a.Bs.Add(b);

EF will manage the linking table updates automatically. Now this is a very basic example, you might want to check the collection to ensure the desired B exists before removing, or doesn't exist before adding, and if dealing with B entities we would need to ensure that any references are tracked by this DbContext instance.

EF can manage linking tables entirely behind the scenes, but in cases where you want more than just an A_Id and B_Id in the associated relationship, such as having an "IsActive" or other details specific to the relationship, you would need to define and map an AB entity. This would look more like:

public class A
{
    public int Id { get; set; }
    public List<AB> ABs { get; } = new();
}

public class B
{
    public int Id { get; set; }
    public List<AB> ABs { get; } = new();
}
    
public class AB
{   
    public int AId { get; set; }  // These IDs need to be mapped as a composite PK.
    public int BId { get; set; }

    public bool IsActive { get; set; } = true; // for example

    public A A {get; set;}
    public B B {get; set;}
}

The advantage of this approach is that you can track information about the relationship between A & B, but the trade-off is that you always need to go through the AB entity to get at the related Bs from A and vice versa. So for instance to add a B to an A you cannot just go a.Bs.Add(b), it needs to be something like a.ABs.Add(new AB { A = a, B = b }) Also when eager loading Bs:

A a = _context.As
    .Include(a => a.ABs)
        .ThenInclude(ab => B)
    .Single(a => a.Id == aId);

Defining linking entities is more flexible, but also a bit more hassle to work with.