Many to many link table entity framework insertion

Question

I have the following relationship: Tables relationship

And the following classes (Only the relevant properties):

1. Affair: (AffairID, Title, ... , AffairsGroups)
2. Group: (GroupID, Name, ... , AffairsGroups)
3. AffairsGroups(AffairID, GroupID, Affairs, Groups)

I want to add a new Affair record to the database which will be also added to the AffairsGroups table (With known GroupID) I've tried it the following way:

private Boolean addAffairToDatabase(Affair affair)
    {
        AffairDal affairContext = new AffairDal();
        affairContext.Affairs.Add(affair);
        affairContext.SaveChanges();

        AffairsGroupDal affairsGroupContext = new AffairsGroupDal();
        affairsGroupContext.AffairsGroups.Add(new AffairsGroup{ AffairID = affair.AffairID , GroupID = user.GroupID});
        affairsGroupContext.SaveChanges();
        return true;
    }

But i get the following exception:

SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.AffairsGroups_dbo.Affairs_AffairID". The conflict occurred in database "NuixTest.DAL.AffairsGroupDal", table "dbo.Affairs", column 'AffairID'.

These are my context classes: 1.

public class AffairDal: DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Affair>().ToTable("Affairs");
    }
    public DbSet<Affair> Affairs { get; set; }
}

2.

public class AffairsGroupDal: DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<AffairsGroup>().ToTable("AffairsGroups");
    }
    public DbSet<AffairsGroup> AffairsGroups { get; set; }
}

What am i doing wrong? Thanks

Answer 1

If your Affairs and Groups are in the same database, you should put their DbSet in the same DbContext.

Furthermore it is good practice to follow Entity Framework Code First Conventions. This way there is no much need to add all kinds of Attributes or Fluent API

Your Affairs and Groups have a fairly straightforward many-to-many relationship: a Group has zero or more Affairs, and every Affair can be member of zero or more Groups.

See Configure Many-to-Many

class Affair
{
    public int Id {set; set;}

    // an Affair can be member of many Groups:
    public virtual ICollection<Group> Groups {get; set;}
    ... 
}

classs Group
{
    public int Id {set; set;}

    // a Group can have many Affairs:
    public virtual ICollection<Affair> Affairs {get; set;}
    ... 
}

class MyDbContext : DbContext
{
    public DbSet<Affair> Affairs {get; set;}
    public DbSet<Group> Groups {get; set;}
}

Entity framework will use table names Affairs and Groups, if you really want to name them Affair and Group without the terminating s, consider using fluent API for this.

Entity Framework automatically detects the many-to-many relationship and will create a third table (AffairsGroups), if you don't like the default name, again, use fluent API.

The nice thing is, that in your normal handling of the many-to-many you don't need the third table. Choose either an Affair and add the Group to it, or choose a Group and add the Affair to it.

using (var myDbContext = ...)
{
    // add an affair that is not in a Group yet:
    var affair1 = myDbContext.Affairs.Add(new Affair()
    {
        Groups = new List<Group>(); // not in any group yet
    });

    // add a Group that has affair1 in it:
    var group1 = myDbContext.Groups.Add(new Group()
    {
        Affairs = new List<Affair>()
        {   // only one member: affair1:
            affair1,
        },
    });
    MyDbContext.SaveChanges();

    // as a test: retrieve affair1 again and see that it suddenly has a Group in it:
    var retrievedAffair = myDbContext.Affairs
        .Where(affair => affair.Id == affair1.Id)
        .Single();
     Debug.Assert(retrievedAffair.Groups.Any());
     Group groupOfAffair = retrievedAffair.Groups.First();
     Debug.Assert(groupOfAffair.Id == group1.Id);
}

So without using the AffairsGroup table you can add an Affair with or without Groups, or a Group with or without Affairs. You won't need the intermediate table or a join using this table.

// get all Affairs that are in a Group with Name:
IEnumerable<Affair> affairs = myDbContext.Groups
    .Where(group => group.Name == Name)
    .Select(group => group.Affairs);