Strange relationship mapping question for NHibernate

Question

I have a unique situation working with a legacy application where I have a parent/child relationship that is based on two integer values. Unfortunately, these fields are not id and parentId or something similar. The columns are ItemId and SubItemId. When these two columns equal each other, the item is assumed to be a parent. When they differ, the ItemId references the Items parent and the SubItemId is simply an identifier of which child it is.

Here is an example ItemId = 1, SubItemId = 1: Parent Item ItemId = 1, SubItemId = 6: Sub Item, whose parent is the Item where the id fields are (1, 1) ItemId = 2, SubItemId = 2: Parent Item ItemId = 2, SubItemId = 9: Sub Item, whose parent is the Item where the id fields are (2, 2)

So with this information, I have a class hierarchy set up like this:

public class Item
    public property ItemId as Integer
    public property SubItemId as Integer
end class

class ParentItem : Item
end class

class SubItem : Item
    public property ParentItem as ParentItem
end class

So I would like to map the ParentItem property of the SubItem class to its corresponding ParentItem using NHibernate and I can't for the life of me figure out how. I was able to get NHibernate to instantiate the correct class based on a formula discriminator, and I was hoping something similar was available for many-to-one relationships.

I'm not able to change the table structure, which certainly limits my options. Also, I'm using FluentNHibernate for my mappings, so feel free to offer suggestions using its syntax.

Thanks!

Update I created a view that added two new columns that equated to a foreign key pointing to the parent and now I'm getting an NHibernate mapping error:

Foreign key (FK163E572EF90BD69A:ItemsNHibernateView [ParentItemID, ParentrSubitemID])) must have same number of columns as the referenced primary key (ItemsNHibernateView [ItemID, SubitemID])

This is throwing me off because to me, it looks like both keys do have the same number of columns...

Answer 1

Ok, I figured it out based on the comment from Ben Hoffstein. I created the view as I mentioned in the update to my original question. The error was caused by an embarassingly stupid error. When I created the array to list the column names for the foreign key, I put both names in the quotes like this:

new string() {"ParentItemID, ParentSubitemID"}

instead of:

new string() {"ParentItemID", "ParentSubitemID"}

The way that NHibernate was displaying the error threw me off the scent.