HasManyToMany list in NHibernate is immutable

Things become very complicated when dealing with many-to-many relationship on legacy database. Our case is Session and Facilitator.

This many-to-many relationship between classes is converted to two 1-N table relationship in database.

One thing surprised us is that NHibernate treats the relationship list data saved in ref table (session_facilitator_tab) as a immutable list, which means, any changes to the list will generate a series of  “delete-all, insert/update” sql statement behind the scene.

            HasManyToMany(x => x.Facilitators)
                .Table("Session_Facilitator_TAB")
                .ParentKeyColumns.Add("Session_ID")
                .ChildKeyColumn("Facilitator_ID")
                .Cascade.All()
                .Not.LazyLoad()
                .Fetch.Select()
                .Not.OptimisticLock()
                ;

In our case, ref table is stored procedure access only, we had to use hybrid mode to merge hbm mapping files for legacy database. Another surprise is that three hbm mapping files (facilitator, session and facilitator_session) must be in hbm format all together, it shouldn’t be like this, a bug from FluentNhibernate?

    .Mappings(m => m.FluentMappings.AddFromAssemblyOf().ExportTo(@"c:\tmp"))
    .Mappings(m => m.HbmMappings.AddFromAssemblyOf())

FluenetNHibernate doesn’t support stored procedure on bag yet, I’m think to submit another patch to FluentNHibernate team a patch for this, the syntax might look like this? I’m sure that SqlUpdate and SqlDelete are not necessary due to immutable list.

            HasManyToMany(x => x.Facilitators)
                .Table("Session_Facilitator_TAB")
                .ParentKeyColumns.Add("Session_ID")
                .ChildKeyColumn("Facilitator_ID")
                .Cascade.All()
                .SqlInsert("sql for insert sp").SqlDeleteAll("sql for delete all sp")
                .Not.LazyLoad()
                .Fetch.Select()
                .Not.OptimisticLock()
                ;






        exec [Session_Facilitator_INS_s]
            @n_Facilitator_ID    = @p1
        ,   @n_Session_ID        = @p0



        exec [Session_Facilitator_DELALL]
            @o_Session_ID        = @p0



The worse scenario is, DBA refuse to create that DELALL SP for us, note that in application we don’t need to create that SessionFacilitator class, for this case we have to pull up this relation ship as a concrete model to get a work-around.

Advertisements

Composite_Id on child collection

We have a legacy database with the table structure like this:

Child model needs to apply composite id, we got this part figured out,

public ChildMap(){
            Table("Child_Tab");
            CompositeId()
                .KeyProperty(x => x.TableName, "Table_Name")                                            
                .KeyReference(x => x.Trainee, "Row_Id");     

but the difficult part is to set up HasMany on parent mapping to filter the data from child based on table_name column.

FluentNHibernate doesn’t allow mismatch composite id setting, which means, if child has composite key, the parent must has the same composite key.

Our final work around is, switch to HasManyToMany, but the middle table still using child table, then we can take advantage of ChildWhere method:

public ParentMap(){
            Table("Parent_Tab");
            Id("Parent_Id");
            HasManyToMany(x => x.Children)
                .Table("Child_Tab")
                .ParentKeyColumns.Add(new []{"Row_Id"})
                .ChildKeyColumns.Add(new[] { "Table_Name", "Row_Id" })
                .ChildWhere("Table_Name = 'Parent_Tab'")
                .Not.LazyLoad()
                ;

Cascade Save-Update in NHibernate and version column

Cascade save-update in NHibernate mapping means saving parent will automatically save child. So instead of calling Session.SaveOrUpdate(personEmail), calling Session.SaveOrUpdate(person) will cover any email change in email list belongs to same person, given cascade save-update is defined at person model mapping level.

Cascade has a few options to choose from.

The interesting point is, cascade save-update will bump up the version column at both parent and child level.  If Person and Email both have a version column defined in table and entity, cascade saving will change both of them.

This will shock many new NHibernate developers. Their reactions are, what if person has many telephones, many addresses, etc, changing any child will touch version column at person table, this will cause more conflict ions in the real world.

Before answers this question, lets pick another example, like, Order and OrderItem, Order has a total amount which is tightly coupled to all OrderItems in same order. Cascading update OrderItem should bump up version column at Order table, otherwise, things just out of sync. That makes sense.

This complies with aggregate root pattern,as one of the aggregate rules is:

When a change to any object within the Aggregate boundary is committed, all invariants of the whole Aggregate must be satisfied.

For me, it means that the version at root level should reflect changes on any child within the same aggregate.

Now the real question is, are email, telephone and address under the same person’s aggregate? If you are shocked to see the person’s version changed by it’s email/telephone/address modification in your app, just don’t map this relationship as cascade save-update, calling Session.SaveOrUpdate(personEmail) instead of Session.SaveOrUpdate(person).

Before setting cascade save-update, read through aggregate root pattern.

Note, I found a very confusing fact, if parent entity has no version column properties defined, cascade save-update will bypass this behavior, but, if  another child entity with version column is mapped as NotLazyLoad collection under the save root, NHibernate will try to set the version column on that child entity!

Generic LinqtoSql Repository

There are some solutions base on Expression and reflection, I think this one is very simple and flexible. Only drawback is decedents must implement IsNew and FetchById, which I think it’s a good thing, because Entity from LinqToSql doesn’t  support inheritance very well.

I made some changes to it to make it support stateless better. The service layer can use TransactionScope to manage transaction (throw exception to rollback).


using System;
using System.Data.Linq;
using System.Linq;

namespace data
{
    public abstract class Repository<T> where T : class
    {
        private readonly IDataContextProvider _conn;

        protected Repository(IDataContextProvider conn)
        {
            _conn = conn;
        }

        public virtual IQueryable<T> GetAll(Func<T, bool> exp)
        {
            var dc = _conn.GetContext();
            // Can not dispose it due to deferred execution
//            using (var dc = _conn.GetContext())
//            {
                return dc.GetTable<T>().Where(exp).AsQueryable();
//            }
        }

        public T Single(Func<T, bool> exp)
        {
            using (var dc = _conn.GetContext())
            {
                return dc.GetTable<T>().Single(exp);
            }
        }

        public void Save(T objectToSave)
        {
            using (var dc = _conn.GetContext())
            {
                if (IsNew(objectToSave))
                {
                    dc.GetTable<T>().InsertOnSubmit(objectToSave);
                }
                else
                {
                    dc.GetTable<T>().Attach(objectToSave, true);
                }
                dc.SubmitChanges();
            }
        }

        /// <summary>
        ///
        /// </summary>
        /// <example>
        /// <code>
        ///      return objectToSave.Id == 0;
        /// </code>
        /// </example>
        /// <param name="objectToSave"></param>
        /// <returns></returns>
        public abstract bool IsNew(T objectToSave);

        public void Delete(T objectToDelete)
        {
            using (var dc = _conn.GetContext())
            {
                dc.GetTable<T>().Attach(objectToDelete, true);
                dc.GetTable<T>().DeleteOnSubmit(objectToDelete);
                dc.SubmitChanges();
            }
            return;
        }
    }

    public class DataContextProvider : IDataContextProvider
    {
        public DataContext GetContext()
        {
            return new DataClasses1DataContext {Log = Console.Out};
        }
    }

    public interface IDataContextProvider
    {
        DataContext GetContext();
    }
}

        [Test]
        [ExpectedException(typeof(NormalRollbackException))]
        public void should_save_modified_object()
        {
            using (new TransactionScope())
            {
                var objectToSave = _systemUnderTest.FectchById(1);
                string newDescription = "sdfd:" + new Random().Next();
                objectToSave.Description = newDescription;

                _systemUnderTest.Save(objectToSave);

                var check = _systemUnderTest.FectchById(objectToSave.Id);
                Assert.AreEqual(newDescription, check.Description);

                throw new NormalRollbackException();
            }
        }

Implement FluentNHibernate join table

I was using the mapping skill found this post to implement FluentNHibernate join table, today trying to upgrade to latest RTM version of FN, failed. The new syntax has been changed to:

            Join("Stakeholder_CV",
                join =>
                {

                    join.Map(prop => prop.StakeholderType, "Stakeholder_Type");
                    join.Map(Reveal.Property<Individual>("Expired"))
                        .Access.CamelCaseField(Prefix.Underscore)
                        .CustomType("string");
                    join.Map(prop => prop.LastApprovalDate, "Last_Approval_Date");
                    join.KeyColumn("Stakeholder_Id");
                });

NH SP, lock, composite Id

1. Set NoCount OFF

Updatable SP needs to turn Set NoCount OFF, NH is very peaky to the response from SP action. Here is an post in Chinese whose example has the line Set NoCount ON in SP commented out. One of the possible problem will be “NHibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect”

According to NH doc:

The stored procedures are by default required to affect the same number of rows as NHibernate-generated SQL would. NHibernate uses IDbCommand.ExecuteNonQuery to retrieve the number of rows affected. This check can be disabled by using check="none" attribute in sql-insert element.

2. Concurrency Check

Timestamp should be placed on top of the property list and right after id/composite id section. Repository/DAO class can then use “session.Lock(attribute, LockMode.Read)” to do concurrency check, throw staleObjectStateException when timestamp doesn’t match.

LockMode.Read just checks version column. It’s a very good solution when using DTO pattern to apply changs from DTO back to domain model. DTO should always carry a original timestamp field, we can do dirty check in service layer before apply changes, but it will be much easier to put the following code into baseRepository:

     <version
             name="LastModifiedDate"
             type="timestamp"
             column="S_Date"
     />

 

public override void SaveOrUpdate(T objToSave)
 {
 // Do not check new objects.
 ConcurrencyCheck(objToSave);

 try
 {
 base.SaveOrUpdate(objToSave);
 }
 catch (Exception ex)
 {
 HandleDatabaseException(ex);
 }

 return;
 }

private void ConcurrencyCheck(T obj)
 {
 if (IsNew(obj)) return;

 if (WasTimeStampCachedBefore())
 {
 if (IsTimeStampChanged(obj))
 throw new ConcurrencyException();

 // Don't try to lock an object with Id. All kinds of exceptions:
 //  1. reassociated object has dirty collection
 return;
 }

 // if we didn't cache timestamp
 try
 {
//                Session.Evict(obj); // Evict will disable update
//                Session.Lock(obj, LockMode.Read);
 using (var session = NHibernateFactory.OpenSession())
 {
 session.Lock(obj, LockMode.Read);
 }
 }
 catch (StaleObjectStateException)
 {
 throw new ConcurrencyException();
 }
 }
        [Test]
        [ExpectedException(typeof(InfrastructureException))]
        public void should_throw_stale_exeption_for_dirty_data()
        {
            var session1 = NHibernateFactory.OpenSession();
            var repository1 = new AttributeRepository(new InMemorySessionStorage(session1));

            var attribute = repository1.FetchByTypeAndCode("003", 8);

            // apply changes from DTO
            attribute.Description = "test data " + new Random().Next();

            // DTO might contains older timestamp
            attribute.LastModifiedDate = ((DateTime) attribute.LastModifiedDate).AddSeconds(-8);

             _repository.SaveOrUpdate(attribute);
        }

Note:  TimeStamp in SQLServer == dotnet Byte[]
Version/TimeStamp in NHibernate == dotnet Datetime  (Datetime if set unsaved-value=nullable)

I added Flush and Evict before Lock to fix the errors of “collection binding to two open sessions” and “child association dirty” problem. Thanks to this post.

Somebody worked very hard to make NHibernate support db.TimeStamp by introducing UserType. Thanks God we don’t have any timestamp in our db.

Notice I could just do a version check out of repository, say service layer, check version number right after retrieve and before applying changes from DTO, as shown in this post 10.4.3 . The idea is similar, and more efficiency actually, only one drawback is that code can’t push down to a base class.

3. Composite Id

Composite Id can be done through a component serializable class overriding Equals and GetHashCode methods.if not using version/timestamp column, unsaved-value should set to any instead of none,  to check it’s a new object when doing SaveOrUpdate().

–update on Dec. 23, 2009.

I’m supprised that composite id actually is very simple, just declare the combination column in hbm file, but the version column still need to set to Generated Never if integrated id is used. I do have a workaround for this, will post later.
sql-insert, update, delete section can split to mulit-lines with “–” as the comment tag to filter out unused properties by SP.Or just use @p0, @p1, @p2 directly instead of using ?,?,?