Things become very complicated when dealing with many-to-many relationship on legacy database. Our case is Session and Facilitator.
This many-to-many relationship between classes is converted to two 1-N table relationship in database.
One thing surprised us is that NHibernate treats the relationship list data saved in ref table (session_facilitator_tab) as a immutable list, which means, any changes to the list will generate a series of “delete-all, insert/update” sql statement behind the scene.
HasManyToMany(x => x.Facilitators) .Table("Session_Facilitator_TAB") .ParentKeyColumns.Add("Session_ID") .ChildKeyColumn("Facilitator_ID") .Cascade.All() .Not.LazyLoad() .Fetch.Select() .Not.OptimisticLock() ;
In our case, ref table is stored procedure access only, we had to use hybrid mode to merge hbm mapping files for legacy database. Another surprise is that three hbm mapping files (facilitator, session and facilitator_session) must be in hbm format all together, it shouldn’t be like this, a bug from FluentNhibernate?
.Mappings(m => m.FluentMappings.AddFromAssemblyOf().ExportTo(@"c:\tmp")) .Mappings(m => m.HbmMappings.AddFromAssemblyOf())
FluenetNHibernate doesn’t support stored procedure on bag yet, I’m think to submit another patch to FluentNHibernate team a patch for this, the syntax might look like this? I’m sure that SqlUpdate and SqlDelete are not necessary due to immutable list.
HasManyToMany(x => x.Facilitators) .Table("Session_Facilitator_TAB") .ParentKeyColumns.Add("Session_ID") .ChildKeyColumn("Facilitator_ID") .Cascade.All() .SqlInsert("sql for insert sp").SqlDeleteAll("sql for delete all sp") .Not.LazyLoad() .Fetch.Select() .Not.OptimisticLock() ;
exec [Session_Facilitator_INS_s] @n_Facilitator_ID = @p1 , @n_Session_ID = @p0 exec [Session_Facilitator_DELALL] @o_Session_ID = @p0
The worse scenario is, DBA refuse to create that DELALL SP for us, note that in application we don’t need to create that SessionFacilitator class, for this case we have to pull up this relation ship as a concrete model to get a work-around.