C#•3mo ago

Many to Many with Entity Framework

I am an absolute newbie to EF, so keep that in mind. I would have thought that if I had the following

class A
{
    public int Id { get; set; }
    public ICollection<B> B { get; set; } = [];  // Edit: forgot the property name
}

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

class A
{
    public int Id { get; set; }
    public ICollection<B> B { get; set; } = [];  // Edit: forgot the property name
}

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

and the following model creation

public DbSet<A> A {get; set;}
public DbSet<B> B {get; set;}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    // B doesn't have a loop back 
    // to A, but it's still a many-to-many relationship
    modelBuilder.Entity<A>()
        .HasMany<B>()
        .WithMany();
}

public DbSet<A> A {get; set;}
public DbSet<B> B {get; set;}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    // B doesn't have a loop back 
    // to A, but it's still a many-to-many relationship
    modelBuilder.Entity<A>()
        .HasMany<B>()
        .WithMany();
}

then if I needed to insert a new A I wouldn't have to do anything other than

await context.AddAsync(newA);
await context.SaveChangesAsync();

await context.AddAsync(newA);
await context.SaveChangesAsync();

but I'm getting an error with

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'B' when IDENTITY_INSERT is set to OFF.

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'B' when IDENTITY_INSERT is set to OFF.

I tried reading through the many-to-many docs but didn't find anything in particular: https://learn.microsoft.com/en-us/ef/core/modeling/relationships/many-to-many I would have thought if the id already existed, then it would be inserting a relationship with the implicitly created mapping table. Is there a different way I should be adding A?

3 Replies

lycian•3mo ago

I'm creating newA basically like so:

var newA = new A();

var allB = context.B.Where(Filter).ToArray();
foreach (var b in allB)
{
    newA.B.Add(b);
}

var newA = new A();

var allB = context.B.Where(Filter).ToArray();
foreach (var b in allB)
{
    newA.B.Add(b);
}

Yawnder•3mo ago

@lycian Your class B would need a reference to A like AId or something. After that, you can even remove the manual mapping since the conventions would be fine already. Last, I've never used .ToArray() for a query, so I don't know if that could messup things or not (even though I wouldn't think so) Last (again): Why are you talking about a many to many relationship? Since you don't have either an Id or a collection, it's hard to see if you actually need one. If it's actually a many-to-many, Instead of the AId I said intially, go with List<A> As and it will create a shadow table for the relationship without you even having to do mapping. (I generally prefer to create my mapping tables though, something like:

class AB
{
  int Id { get; set; }  // <-- Optional
  int AId { get; set; }
  A A { get; set; }
  int BId { get; set; }
  B B { get; set; }
}

class AB
{
  int Id { get; set; }  // <-- Optional
  int AId { get; set; }
  A A { get; set; }
  int BId { get; set; }
  B B { get; set; }
}

)

lycian•3mo ago

The mapping back is optional but the logistics is still many to many. I can try adding the collection to B. It just won't really be used moved this to #database for those who are curious

Gaming

Programming

Many to Many with Entity Framework