key-many-to-one and key-property association: nhibernate won't DELETE items from set

Question

I'll try to keep this terse, but hopefully won't miss any important information in my troubles. The code I believe provides all details, but I've left out the noise (it's VB, so there's lots of noise :) ).

A "Case" object has many "Assignments":

Public Class Case
  Property CaseId As Guid 
  Property Assignments As ISet(Of CaseAssignment)
End Class

Public Class CaseAssignment
  Property Case As Case
  Property RoleId As Guid
End Class

The data model I've been handed looks about like what you'd expect, except CaseAssignment is a composite key:

table Case
   CaseId uniqueid not null primary key
   ...

table CaseAssignment
   CaseId uniqueid not null
   RoleId uniqueid not null
   PK := (CaseId, RoleId)
   FK from CaseId -> Case.CaseId

Finally, the Fluent NHibernate Mappings:

Class CaseMapping
  Public Sub New()
    Table("Case")
    KeyColumn("CaseId")
    HasMany(Function(x) x.Assignments).KeyColumn("CaseId").Cascade.AllDeleteOrphan()
End Class

Class CaseAssignmentMapping
  Public Sub New()
    Table("CaseAssignment")
    CompositeId() _
      .KeyReference(Function(x) x.Case, "CaseId") _
      .KeyProperty(Function(x) x.RoleId)
End Class

KeyReference correlates with "key-many-to-one" in the XML mapping lingo.

When I add assignments to a case all is good, but when I remove references I get one of two problems. With this code:

aCase.Assignments.Remove(someAssignment)
caseRepository.Save(aCase)

The error I get back is, "Could not delete collection rows... Cannot insert the value NULL into column 'CaseId', table 'CaseAssignments'; column does not allow nulls. UPDATE fails. The statement has been terminated." This was from trying to issue the following SQL:

UPDATE CaseAssignments SET CaseId = null
WHERE CaseId = @p0 AND RoleId = @p1 AND CaseId = @p2
@p0=[valid guid #1],
@p1=[valid guid #2],
@p2=[valid guid #1 again] **!?!**

So that's a little messed up. So I try this code:

aCase.Assignments.Remove(someAssignment)
someAssignment.Case = Nothing
caseRepository.Save(aCase)

and the error is "Unexpected row count: 0; expected: 1" because NHibernate tried to: DELETE FROM CaseAssignments WHERE RoleId = [valid guid] AND CaseId = NULL

I've been scouring threads and forums and the NHibernate and Hibernate docs and haven't really come across anything similar yet. Hopefully it's something simple. Thanks to anyone who takes a shot at this one!

Answer 1

I had the same problem a few days ago. The solution is to put the "inverse" attribute to "true" on your collection of CaseAssignments in your CaseMapping class. Like this:

HasMany(Function(x) x.Assignments).KeyColumn("CaseId").Cascade.AllDeleteOrphan().Inverse()

As far as I know, you must have both the AllDeleteOrphan cascade type AND the Inverse property set to true for this to work.

I hope it works!