nHibernate ManyToManyToMany with Fluent

Question

I have a structure here I have a ManyToManyToMany relationship.

This is my (truncated) fluent mappings.

public AgencyMap() 
{
    Id(x => x.AgencyId, "AgencyId");      
    Map(x => x.AgencyCode, "AgencyCode");
    HasManyToMany<Personnel>(x => x.Personnel)
    .WithTableName("AgencyPersonnel")
    .WithParentKeyColumn("AgencyId")
        .WithChildKeyColumn("PersonnelId").Cascade.All().LazyLoad();
}

public PersonnelMap() 
{
    Id(x => x.PersonnelId, "PersonnelId");    
    HasManyToMany<Discipline>(x => x.Disciplines)
        .WithTableName("AgencyPersonnelDiscipline")
        .WithParentKeyColumn("AgencyPersonnelId")
        .WithChildKeyColumn("DisciplineId")
        .Cascade.SaveUpdate().LazyLoad();
}     

public DisciplineMap() 
{
    SchemaIs("dbo");      
    Id(x => x.DisciplineId, "DisciplineId");      
    Map(x => x.DisciplineCode, "DisciplineCode");     
    Map(x => x.Description, "Description");      
}

If I then run code like this.

Agency agency = m_AgencyRepository.Get(10);
var personnel = new Personnel() { ... };
personnel.Disciplines.Add(new Discipline() { ... });
agency.Personnel.Add(personnel);
m_AgencyRepository.Save(agency);

When I run this code I get this error.

could not insert collection: [PPSS.Model.Personnel.Disciplines#22][SQL: SQL not available]
The INSERT statement conflicted with the FOREIGN KEY constraint "AgencyPersonnel_AgencyPersonnelDiscipline_FK1". The conflict occurred in database "PPSS2", table "dbo.AgencyPersonnel", column 'AgencyPersonnelId'.\r\nThe statement has been terminated.

Enitities look like.

public class Agency
{
  public virtual int AgencyId {get; set;}
  public virtual IList<Personnel> Personnel {get; set;}
}

public class Personnel
{
  public virtual int PersonnelId {get; set;}
  public virtual IList<Agency> Agencies {get; set;}
  public virtual IList<Dependency> Dependencies {get; set;}
}

public class Dependency
{
  public virtual int DependencyId {get; set;}
  public virtual string Name {get; set;}
}

If I add an inverse to the ManyToMany in personnel then the Personnel is saved but not the disciplines (no exception is raised).

How should this mapping be done?

Edit:

I have got some more info. If I disable the constraint AgencyPersonnel_AgencyPersonnelDiscipline_FK1 then it all is inserted ok. This makes me think it is the order nHibernate is inserting that is the problem. What I would expect it to do in order is.

1. INSERT INTO Personnel
2. Get last key (PersonnelId)
3. INSERT INTO AgencyPersonnelDiscipline with AgencyId and PersonnelId.

This would not violate any constraints.

Answer 1

Since this is a truncated mapping I'm gonna take a shot in the dark :). This kind on of exception usually happens to me when I have 2 entities : X and Y , and I map them from both directions, something like :

X has a property Y

Y has a list of X

And none of them has the Inverse() attribute set. Check if you're mapping something in both directions but not setting one on them Inverse().

Answer 2

Given that the maps you have are correct for the types you state, you haven't mapped back from Personnel to Agency or from Discipline to Personnel.