Fluent-Nhibernate Many-To-Many relationshiop issue

Question

I am facing an issue while trying to delete a many-to-many relationship using Fluent Nhibernate. I have following domain classes:

public class Organisation
{
   public virtual int Id {get; set;}

   private IList<OrganisationRelationshiop> relatedOrganisations; 

   public virtual IList<OrganisationRelationship> RelatedOrganisation
   {
       get
       {
           return this.relatedOrganisations;
       }

      protected set
      {
           this.relatedOrganisations = value;
      }
    }


    public virtual void RemoveRelatedOrganisation(OrganisationRelationship organisationRelationship)
    {
        this.relatedOrganisations.Remove(organisationRelationship);
    }

}

Here is my OrganisationRelationship class that is representing the many to many relationship between orgnaisations.

   public class OrganisationRelationship 
   {
       public virtual int Id {get; set;}

       public virtual Organisation Organisation{ get; set; }

       public virtual OrganisationRelationshipType OrganisationRelationshipType { get; set; }

       public virtual Organisation RelatedOrganisation { get; set; }
   }

Her is the script for tables:

Organisation Table:

CREATE TABLE [dbo].[Organisation](
[Id] [int] IDENTITY(1,1) NOT NULL,
[OrganisationName] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_Organisation] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

OrganisationRelationshop Table:

CREATE TABLE [dbo].[OrganisationRelationship](
[Id] [int] IDENTITY(1,1) NOT NULL,
[OrganisationId] [int] NOT NULL,
[RelatedOrganisationId] [int] NOT NULL,
[OrganisationRelationshipTypeId] [int] NOT NULL,
CONSTRAINT [PK_OrganisationRelationship] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,        ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

OrganisationRelationType Table:

CREATE TABLE [dbo].[OrganisationRelationshipType](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_OrganisationRelationshipType] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Everything is working as I expect but when I try to delete a relationship, fluent Nhibernate tries to set the CompanyId to null for that particular record instead of deleting the record from the CompanyRelationship table. Here is the query that I can see in NHProf:

UPDATE CompanyRelationship
SET    CompanyId = null
WHERE  CompanyId = 3893 /* @p0 */
AND Id = 487 /* @p1 */

To delete the record I am calling RemoveRelatedCompany function which removes the particular CompanyRelationship from the relatedCompanies list and then I am calling Session.Save() and Session.Flush() to save the Company entity.

Any ideas as to what I am doing wrong here due to which this behavior?

Answer 1

Probably you must set .Cascade.AllDeleteOrphan(); (cascade=“all-delete-orphan”) on your mappings.

Also if you post your mappings will be easier to answer you

Answer 2

As I am using AutoMapping I had to overrid the mappings for the Organisation as follows to resolve the issue:

mapping.HasMany(c => c.RelatedOrganisations).Inverse().ForeignKeyCascadeOnDelete().Table("OrganisationRelationship");