nHibernate "HasMany" without explicit FKs

Question

I've got a data model that I'm not sure fluent nHibernate supports - was wondering if anyone could tell me whether it does, and if so, how to do it. The basic structure is:

create table Container (
  id int identity(1,1) NOT NULL,
  root_item_id int
)

create table ItemRelationship (
   id int identity(1, 1) NOT NULL,
   parent_item_id INT,
  child_item_id INT
)

create table Item (
   id int identity(1, 1) NOT NULL,
   description VARCHAR(20)
)

So in a nutshell: 1) Container has a Root Item 2) Items can have children Items

What I want is a property on my "Container" entity that is a Collection of the Items that are the CHILDREN of it's Root Item. I can see how to setup "direct" FK relationships, but this one is a little unusual, as the chain of relationship is:

Container.root_item_id -> ItemRelationship.parent_item_id

There's not an explicit FK there. I'm assuming I have to use the "Where" method in some fashion, but am not sure how - wasn't able to find examples. Any ideas?

Answer 1

I think your problem can be solved with a relatively easy convenience property; Let me see if I have this right:

You have a Many to Many relationship between Items (maintained in the ItemRelationship table). Therefore, by default you should be able to define an Item class with a property on it for ChildItems(your ClassMap for this will have a HasManyToMany() call in it). Your Container class will have a Root_Item property of type Item (which as just mentioned has a ChildItems list). Then you can make a convenience property on Container that returns the ChildItems of its Root_item. eg:

public class Container
{
    public virtual Item Root_Item { get; set; }

    public List<Item> ChildItems
    {
        get {
            if (Root_Item != null)
                 return Root_Item.ChildItems;
            return new List<Item>(); // or null depending on how you want to handle this
        }
    }
}