Cascading with Castle ActiveRecord HasAndBelongsToMany's

Question

I'm having a lot of trouble cascading deletes through a HasAndBelongsToMany relationship using Castle ActiveRecord/NHibernate.

I have Photos which have and belong to many Tags. They are joined by a table called PhotoHasTag which just has a photoId and tagId. When I delete a Photo, I'd like all associated PhotoHasTag entries to be deleted, and any then orphaned Tags to be deleted as well.

Right now I have my Photo class setup something like this:

    [ActiveRecord(Table="Photo")]
    public class Photo
    {
        [PrimaryKey(Column = "photoId", Generator = Castle.ActiveRecord.PrimaryKeyType.Identity)]
        public virtual int Id { get; set; }    

        [HasAndBelongsToMany(Table="PhotoHasTag",ColumnKey="photoId",ColumnRef="tagId",Lazy=true,Cascade=ManyRelationCascadeEnum.AllDeleteOrphan)]
        public virtual IList<Tag> Tags { get; set; }
    }

And my Tag class is setup pretty much the same way:

    [ActiveRecord(Table="Tag")]
    public class Photo
    {
        [PrimaryKey(Column = "tagId", Generator = Castle.ActiveRecord.PrimaryKeyType.Identity)]
        public virtual int Id { get; set; }  

        [HasAndBelongsToMany(Table = "PhotoHasTag", ColumnKey = "tagId", ColumnRef = "photoId", Lazy = true)]
        public IList<Photo> Photos { get; set; }
    }

When I try to remove photo's I end up getting an error from SQL Server:

The DELETE statement conflicted with the REFERENCE constraint "PhotoHasTag_FK1".

I can step around this in SQL Server by setting the Delete Rule on the Key to Cascade, but then only the PhotoHasTag is deleted. If there are any orphaned Tags they will still be lingering in the database.

I am considering writing a Trigger to take care of the left over Tags, but I would be much happier if there is a way to setup my ActiveRecord mappings so that the delete cascades down.

Thanks in advance, I've been stuck on this forever so I appreciate any and all help!

Answer 1

You can specify cascade behaviour in the attributes like so:

[HasAndBelongsToMany(Table = "PhotoHasTag", Cascade=ManyRelationCascadeEnum.AllDeleteOrphan, ColumnKey = "tagId", ColumnRef = "photoId", Lazy = true)]
public IList<Photo> Photos { get; set; }

The API documentation is probably more helpful.