NHibernate, "On Delete Cascade", cascade deleting rows in related tables?

Question

I've been using NHibernate (with Fluent-NHibernate mappings) in a project for the first time over the last few weeks, all was going well until today when I've hit a problem (most probably my own error).

I've made a small sample to illustrate what I'm trying to achieve:

public class Image
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string Path { get; set; }
}

public class FeaturedImage
{
    public virtual int Id { get; set; }
    public virtual Image Image { get; set; }
    public virtual string Description { get; set; }
    public virtual DateTime Date { get; set; }
}

public class ImageMap : ClassMap<Image>
{
    public ImageMap()
    {
        Id(x => x.Id).GeneratedBy.Identity().UnsavedValue(0);
        Map(x => x.Name);
        Map(x => x.Path);
    }
}

public class FeaturedImageMap : ClassMap<FeaturedImage>
{
    public FeaturedImageMap()
    {
        Id(x => x.Id).GeneratedBy.Identity().UnsavedValue(0);
        Map(x => x.Description);
        Map(x => x.Date);
        References(x => x.Image).Not.Nullable().Cascade.Delete();
    }
}

In the example above there are multiple images, each day one image gets picked as a "featured" image, the same image can be featured multiple times. The behaviour I would like is when I delete an image any FeaturedImage entries referencing that image ID automatically get deleted. However, at the moment if I try to delete an Image which has been featured it throws an error:

The DELETE statement conflicted with the REFERENCE constraint "FKF42D8269692640D". Conflict table "dbo.FeaturedImage", column 'Image_id'.

If If manually add "ON DELETE CASCADE" to the image id foreign key constraint it works:

alter table [FeaturedImage]
 add constraint FKF42D8269692640D foreign key ( Image_id ) references [Image] ON DELETE CASCADE

...but I'm not sure if this is the recommended way of doing this? If anyone could advise the best way of achieving this it would be greatly appreciated! Thanks in advance.

Answer 1

The .Cascade.Delete() you added is in the wrong side of the relationship, and it would actually cause the Image to be deleted when you delete a FeaturedImage.

What you want to do can be accomplished by creating a collection of FeaturedImage in Image, map it as an inverse bag with cascade-deleting in the key.

I don't use Fluent, but in XML you set on-delete="cascade" in the collection <key> element; look for something similar.