Fluent NHibernate: Cascade delete from one side only on Many-to-Many relationship

Question

Hi All,

I'm trying to write the proper map configuration that will allow me to delete from only one side on a Many-to-Many relationship setup.

Below is the code structure for my Map and Entity class along with the actual program (psuedo) code and SQL schema. Fairly simple and straight forward.

We have a person table, and a file table. We then have a personfile table since a person can have many files and likewise, a file can be assigned to many persons.

Now, when I delete a person record, the related records in personfile and file belonging to the person are deleted. So far so good.

If I delete a file (as shown belowin Program.cs), I want it to delete from personfile and file but NOT from person.

However with the way I have things setup, NHibernate only calls the delete on file table which causes an error. Eg.

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`personfile`, CONSTRAINT  `FK_PersonFile2` FOREIGN KEY (`FileID`) REFERENCES `file` (`FileID`))

I did add Cascade.Delete() to FileMap but when i did that, deleting from file table also deletes from person table.

To reiterate, what I ultimately want is to call Delete(file) which in turn will delete the records from personfile table and file table but NOT person table.

Should I instead be going the route of getting the person record and then removing the file record from person.Files[] collection and then calling SaveOrUpdate()?

Consider the scenario.

First make sure we have good data in all tables.

mysql> SELECT f.FileID, p.PersonID, p.Name, f.Filename
    -> FROM personfile pf
    ->   LEFT OUTER JOIN file f on pf.FileID = f.FileID
    ->   LEFT OUTER JOIN person p on pf.PersonID = p.PersonID
    -> ;
+--------+----------+------+-------------+
| FileID | PersonID | Name | Filename    |
+--------+----------+------+-------------+
|      1 |        1 | John | Apples.jpg  |
|      2 |        1 | John | Oranges.jpg |
|      3 |        2 | Bob  | Grapes.jpg  |
+--------+----------+------+-------------+
3 rows in set (0.00 sec)

Now in a normal situation if you try to delete file only (which is what NHibernate is trying to do based on my setup) this is what happens which is expected.

mysql> DELETE FROM file WHERE file.FileID = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`pe
rsonfile`, CONSTRAINT `FK_PersonFile2` FOREIGN KEY (`FileID`) REFERENCES `file` (`FileID`))

What I want NHibernate to do is something like this, where first the records in the relation table is deleted then the record in the atual file table. The query doesnt' have to be specific, as long as the end result is same.

mysql> DELETE pf, f
    -> FROM personfile pf
    ->   LEFT OUTER JOIN file f on pf.FileID = f.FileID
    ->   LEFT OUTER JOIN person p on pf.PersonID = p.PersonID
    -> WHERE pf.FileID = 2
    -> ;
Query OK, 2 rows affected (0.05 sec)

The result of the above delete is valid.

mysql> SELECT f.FileID, p.PersonID, p.Name, f.Filename
    -> FROM personfile pf
    ->   LEFT OUTER JOIN file f on pf.FileID = f.FileID
    ->   LEFT OUTER JOIN person p on pf.PersonID = p.PersonID
    -> ;
+--------+----------+------+------------+
| FileID | PersonID | Name | Filename   |
+--------+----------+------+------------+
|      1 |        1 | John | Apples.jpg |
|      3 |        2 | Bob  | Grapes.jpg |
+--------+----------+------+------------+
2 rows in set (0.00 sec)

Program.cs

File file = db.Session.Load<File>(2);

session.Delete(file);

transaction.Commit();

Mapping

public class FileMap : ClassMap<File>
{
  public FileMap()
  {
    Id(x => x.FileID)
      .GeneratedBy.Identity();
    Map(x => x.Filename)

    HasManyToMany(x => x.Persons)
      .Table("PersonFile")
      .Inverse()
      .ParentKeyColumn("FileID")
      .ChildKeyColumn("PersonID");
  }
}

public class PersonMap : ClassMap<Person>
{
  public PersonMap()
  {
    Id(x => x.PersonID)
      .GeneratedBy.Identity();
    Map(x => x.Name)

    HasManyToMany(x => x.Files)
      .Table("PersonFile")
      .Cascade.Delete()
      .ParentKeyColumn("PersonID")
      .ChildKeyColumn("FileID");
  }
}

Entity

public class File
{
  public virtual uint FileID { get; set; }
  public virtual string Filename { get; set; }

  public virtual IList<Person> Persons { get; set; }
}

public class Person
{
  public virtual uint PersonID { get; private set; }
  public virtual string Name { get; set; }

  public virtual IList<File> Files { get; set; }
}

SQL

CREATE TABLE `file` (
  `FileID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`FileID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `person` (
  `PersonID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`PersonID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `personfile` (
  `PersonID` int(11) unsigned NOT NULL DEFAULT '0',
  `FileID` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`PersonID`,`FileID`),
  KEY `FK_PersonFile2` (`FileID`),
  CONSTRAINT `FK_PersonFile1` FOREIGN KEY (`PersonID`) REFERENCES `person` (`PersonID`),
  CONSTRAINT `FK_PersonFile2` FOREIGN KEY (`FileID`) REFERENCES `file` (`FileID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 1

Try calling File.Persons.Clear() to remove the linked Persons from a File before deleting it.

I'm curious about this requirement; the 2nd sentence seems to contradict it because you could delete File records that are linked to a different Person than the one you're deleting.

We have a person table, and a file table. We then have a personfile table since a person can have many files and likewise, a file can be assigned to many persons.

Now, when I delete a person record, the related records in personfile and file belonging to the person are deleted. So far so good.