NHibernate many-to-many with composite element

Question

Hello everyone.

I encountered the following problem. In my application uses the entities Owner and Area binding as many to many.

public class Area : DomainObject<int> {        
    private ISet<OwnersPeriod> _owners = new HashedSet<OwnersPeriod>();

    public ICollection<OwnersPeriod> Owners {
        get { return _owners; }
        set {
            Check.Require(value != null);
            _owners = new HashedSet<OwnersPeriod>(value);
        }
    }
}

Table Owner2Area has the following fields:

CREATE TABLE [dbo].[Owner2Area](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IDOwner] [int] NOT NULL,
    [IDArea] [int] NOT NULL,
    [FirstDate] [datetime] NOT NULL,
    [LastDate] [datetime] NULL,
 CONSTRAINT [PK_Owner2Area] PRIMARY KEY CLUSTERED)

Therefore corresponds to the class OwnersPeriod

    public class OwnersPeriod {
        private Owner _member;
        private Area _area;    
        public Owner Owner { get {...} set{...} }    
        public Area Area { get { ... } set { ... } }    
        public  DateTime FirstDate { get; set; }    
        public  DateTime? LastDate { get; set; }       
    }

I wrote mappings

<class lazy="false" name="Core.Domain.Area, Core" table="Areas">
   ...
   <set name="Owners" table="Owner2Area" inverse="true" lazy="true" >
      <key column="IDArea"/>
      <composite-element class="Core.Domain.OwnersPeriod, Core" >
        <parent name="Area" />
        <property name="FirstDate" type="datetime"/>
        <property name="LastDate" type="datetime"/>
        <many-to-one name="Owner" class="Core.Domain.Owner, Core" column="IDOwner"/>
      </composite-element>
    </set>
</class>

For each area existing data are successfully loaded into Owners, but when I add new record in Owner2Area through CreateSQLQuery, these data are not updated for instance of area. If I re-opened the form and got all areas, added link successfully loaded into the collection.
How can be forced to load added thus recording of relation many-to-many?
Nhibernate v.2.0.1, db MSSQL 2005

Answer 1

If you use CreateSQLQuery NHibernate doesn't know what you did. Just think about it for a minute. CreateSQLQuery was added because not all things are possible with HQL and CriteriaQuery. So with CreateSQLQuery you do 'special things'.

Many-to-many is not recomended, because you don't have a 'direct handling' of the 'middle table'. If you'd add an entity to a many-to-many relation the nhibernate way (modifying a collection) and then doing an update of the entity, you would see that nhibernate deletes all data from the 'middle table' and then inserts it again. So not good. That's why you probably thought that you'd do a workaround using CreateSQLQuery. I suggest you do classic parent-child relation with 3 entities, so you can directly manipulate the 'middle table'.

The other solussion is that when you update the 'middle table' you do an Evict (that's a method of ISession and a method in ISessionFactory, check the manual) of entity that's beeing affected with CreateSQLQuery. But using this will force another hit to a database (a new version will be loaded). Using the parent-child approach will not do a hit to a database.