Fluent Nhibernate how to set values for not null columns during cascade save

Question

I have tables with following relationship:

public class Physicians
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual List<Specialty> Specialties{ get; set; }
    public virtual User CreatedBy { get; set; }
}

public class Specialties
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }    
    public virtual User CreatedBy { get; set; }
}


public class PhysicianSpecialtyBridge
{
   public virtual Physicians Physician{get; set;}
   public virtual Specialties Specialty { get; set; }
   public virtual User CreatedBy { get; set; }
}

In the mapping i have specified as

 HasManyToMany(physician => physician.Specialties).Table("PhysicianSpecialty")
.ParentKeyColumn("Physician").ChildKeyColumn("Specialty").Cascade.All;

The issue i have when i try to associate the Physician with the list of specialties and do a save, it is failing while trying to insert into the bridge table because the createdby key in PhysicianSpecialtyBridge is not null. When i make the column nullable, everything works fine. Any solution for this problem? For some reason i need to maintain "CreatedBy" in my bridge table as well.

Answer 1

The issue here is, that while you are using many-to-many mapping, the C# Entities represent different relation. Many-to-many is used in scenarios, when 1) we have a pairing table, but 1) there is no C# Entity for its represenation.

A) I would suggest, if you want to keep the pairing object as C# entity to change your model. It should be like this:

1) The pairing table should have its own surrogated key

public class PhysicianSpecialtyBridge
{
   // the pairing object should have the ID
   public virtual int Id { get; set; }
   public virtual Physician  Physician { get; set; }
   public virtual Speciality Speciality{ get; set; }
   ...

2) the other object should list the pairing object (NOTE I used singular for entity names)

public class Physician
{
    ...
    public virtual List<PhysicianSpecialtyBridge> Specialties{ get; set; }


public class Specialty
{
    ...
    public virtual List<PhysicianSpecialtyBridge> Physicians{ get; set; }

Then the mapping of the Pairing object would be like

References( x => x.Physician);
References( x => x.Specialty);

The Physicians is now in relation one-to-many to pairing object

HasMany(x => x.Specialties)
  .Inverse()
  .Cascade.All(); 

The Specialties is similar

HasMany(x => x.Physicians)
  .Inverse()
  .Cascade.All(); 

B) If you really want many-to-many, then you have to remove the Pairing object. The C# Entites will be like this:

public class Physician
{
    ...
    public virtual List<Specialty> Specialties{ get; set; }


public class Specialty
{
    ...
    public virtual List<Physician> Physicians{ get; set; }

And the mapping

The Physicians

HasManyToMany(x => x.Specialties)
  .Table("PhysicianSpecialty")
  .ParentKeyColumn("Specialty")
  .ChildKeyColumn("Physician")
  .Inverse()
  //.Cascade.All() - NO Cascade, pairing table will be managed by default
  ; 

The Specialties

HasManyToMany(x => x.Physicians)
  .Table("PhysicianSpecialty")
  .ParentKeyColumn("Physician")
  .ChildKeyColumn("Specialty")
  // .Inverse() - NOT INVERSE, only one can be
  // .Cascade.All() - NO Cascade, that will remove the Speciality
  ; 

The A) approach is better and suggested. One of advantages is, that your pairing object could have more properties (e.g. CreatedBy or Order ... IsActive)... and you also gain the ability to search with subqueries for Physcians having some type of Specialities

Please check Chapter 24. Best Practices. Extract

Don't use exotic association mappings.

Good usecases for a real many-to-many associations are rare. Most of the time you need additional information stored in the "link table". In this case, it is much better to use two one-to-many associations to an intermediate link class. In fact, we think that most associations are one-to-many and many-to-one, you should be careful when using any other association style and ask yourself if it is really neccessary.

Also you can check Nhibernate: How to represent Many-To-Many relationships with One-to-Many relationships? for more details about the alternative of the many-to-many. Some reading about advantages of subqueries could be found here https://stackoverflow.com/a/14080092/1679310