Manually managing a join table in Castle ActiveRecord

Question

I have two connected classes defined like this:

    [ActiveRecord]
    public class Store : ActiveRecordBase<Store> {
        [PrimaryKey]
        public int ID { get; set; }

        [HasAndBelongsToMany(Table = "StoreCustJoin",
                             ColumnKey = "storeID",
                             ColumnRef = "customerID")]
        public IList<Customer> customers { get; set; }
    }

    [ActiveRecord]
    public class Customer : ActiveRecordBase<Customer> {
        [PrimaryKey]
        public int ID { get; set; }

        [HasAndBelongsToMany(Table = "Join",
                             ColumnKey = "customerID",
                             ColumnRef = "storeID",
                             inverse = true)]
        public IList<Store> stores { get; set; }
    }

    [ActiveRecord]
    public class Join : ActiveRecordBase<Join> {
        [PrimaryKey]
        public int ID { get; set; }

        [BelongsTo("storeID")]
        public Store store { get; set; }

        [BelongsTo("customerID")]
        public Customer customer { get; set; }

        [Property]
        public int Status { get; set; }
    }

When I connect a Store to a Customer I need to set the status as well. So I tried to do this:

    public void SetLink(Store theStore, Customer theCustomer, int status) {
        var link = new Join()
                       {
                           Status = status,
                           store = theStore,
                           customer = theCustomer
                       };
        theStore.customers.Add(theCustomer);
        theCustomer.stores.Add(theStore);
    }

This creates two entries in ABJoin, the first with the status set, the second without. If I don't add the objects to their respective lists, it works. But then the link is not reflected in these objects until the current session is closed and the instance is re-downloaded from the DB.

So, is there a way I can set the Status when I create the link and also keep the current objects valid and up to date?

Answer 1

When your relationship table (in your case the "Join" table) has additional fields other than the FKs to the tables it relates, use [HasMany] instead of [HasAndBelongsToMany], i.e.:

[ActiveRecord]
public class Store : ActiveRecordBase<Store> {
    [PrimaryKey]
    public int ID { get; set; }

    [HasMany]
    public IList<Join> customers { get; set; }
}

[ActiveRecord]
public class Customer : ActiveRecordBase<Customer> {
    [PrimaryKey]
    public int ID { get; set; }

    [HasMany]
    public IList<Join> stores { get; set; }
}

Similar questions:

• http://stackoverflow.com/questions/350311/using-additional-data-on-intermediate-table-with-nhibernate
• http://stackoverflow.com/questions/2713329/nhibernate-many-to-many-relationship-with-field-in-the-relationship-table