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()
                ;

FluentNHibernate AutoMapping

Official document: http://wiki.fluentnhibernate.org/Auto_mapping

We use an AutoPersistenceModelGenerator to wire up automapping with config.


config.Mappings(m => m.AutoMappings.Add(new AutoPersistenceModelGenerator(typeof(T).Assembly).Generate()));

We also might have some unique conventions. e.g., PropertyName => Column_Name, EntityName=>EntityName_TAB, Id=>EntityName_ID, etc, all can be defined in AutoPersistenceModelGenerator.


    public class AutoPersistenceModelGenerator
    {
        private readonly Assembly _assemblyContainsEntities;

        public AutoPersistenceModelGenerator(Assembly assemblyContainsEntities)
        {
            _assemblyContainsEntities = assemblyContainsEntities;
        }

        public AutoPersistenceModel Generate()
        {
            var mappings = new AutoPersistenceModel();
            mappings.AddEntityAssembly(_assemblyContainsEntities).Where(GetAutoMappingFilter);
            mappings.Conventions.Setup(GetConventions());
            mappings.Setup(GetSetup());
            mappings.UseOverridesFromAssembly(_assemblyContainsEntities);
            return mappings;
        }

        private Action<AutoMappingExpressions> GetSetup() {
            return c => {
                c.FindIdentity = type => type.Name == "Id";

            };
        }

        private Action<IConventionFinder> GetConventions()
        {
            return c =>
            {
                c.Add(ConventionBuilder.Id.Always(x =>
                {
                    x.Column(x.EntityType.Name.ToUpper() + "_ID");
                    x.CustomSqlType("INT"); // otherwise our long type id will be automapped to BIGINT
                }));
                // other way:                c.Add(PrimaryKey.Name.Is(x => x.EntityType.Name + "_ID"));
                // or                        c.Add<PrimaryKeyConvention>();

                c.Add(Table.Is(x => x.EntityType.Name + "_TAB"));
                // The above line can also be replaced by TableNameConvertion
                //                c.Add<TableNameConvention>();</pre>
                c.Add(ForeignKey.EndsWith("_ID"));
                // or                c.Add<Infrastructure.Convertions.ForeignKeyConvention>();
                c.Add<ManyToManyTableNameConvention>();
                c.Add<UnderstoreColumnNameConvention>();
                c.Add<StringPropertyConvention>();
                c.Add<YesNoPropertyConvention>();
                c.Add<AuditPropertyConvention>();
          };
    }

    private bool GetAutoMappingFilter(Type t)
    {
      return t.GetInterfaces().Any(x => x == typeof(IAutoMap) );
    }
 }

 public class UnderstoreColumnNameConvention : IPropertyConvention, IPropertyConventionAcceptance
    {
        public void Apply(IPropertyInstance instance)
        {
            instance.Column(Inflector.Net.Inflector.Titleize(instance.Name).Replace(" ", "_"));
        }

        public void Accept(IAcceptanceCriteria<IPropertyInspector> criteria)
        {
            criteria.Expect(propertyInspector => !AuditPropertyConvention.IsAuditProperty(propertyInspector.Name));
        }
    }

1st day of NHProf

Finally, I got some time to try this must-have tool for NHibernate, downloaded it, asked for the trail key, why 33 days?

No changing to source code sounds a good idea, the assumption is using log4net appender, every NH project suppose use log4net, doesn’t it?

I’m not sure what NHibernate internal statistics is? (Ayende doesn’t recommend turn this on) Because we are using FluentNHibernate, there is no hibernate.cfg.xml file we can change, there is 2 solutions from stack overflow, but without seeing this xml file, how can I know it works? (BTW, how can I export hibernate.cfg.xml to somewhere like what we can do for hbm.xml?)

The result is, I can’t output statistics file for NHprof, so instance is the only way. Fortunately, it works. But, what if the port is being used? Or how to modify it? I couldn’t find it in NHProf.exe.config, it can’t be hardcoded?

     tcp://127.0.0.1:22897

Input from Ayende: Changing the port that the profiler listen to is done through the UI, Options > Settings

OK, back to our app, 5 Unbounded result set warnings and one N+1 select alert. Not too bad, the interesting part is, this N+1 select alert only appears in WCF client, not in domain service or repository layer.

It turns out the problem happens in AutoMapper which is doing the property loop through the result set from repository. Some properties are set to lazy-load.

We should be  really careful when using AutoMapper, I think we should either explicitly exclude those lazy-load properties, or create a query to eager fetch those needed properties.

Thanks to NHProf, developers can figure out what’s extatly beneath their feet, ice or solid ground.

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");
                });

Create your own Parts to merge Stored Procedure into Fluent NHibernate mappings

Update: This patch has been merged into Fluent NHibernate offiical release V1.1.

NOTE:  I created a patch to enable you can use stored procedure in RTM version (r647).   As they mentioned in FN google group, this patch has been merged into Fluent NHibernate trunk in November 2009, as the change log shown.

New syntax:

SqlInsert(INSERT_SP).Check.None();
SqlUpdate(UPDATE_SP).Check.None();
SqlDelete(DELETE_SP).Check.None();

Ryan’s solution is a very high level usage of Fluent NHibernate. By creating a customized StoredProcedurePart class implementing IMappingPart interface, developer gained some kind of control to merge their Stored Procedure definition into FN mappings.

A useful trick:

Export hbm file to somewhere say temp folder for debug purpose, you don’t trust any new guys.


            var config = Fluently.Configure()
                .Database(
                MsSqlConfiguration.MsSql2005
                    .ConnectionString(x =&gt; x.FromConnectionStringWithKey(connectionStringKey))
                    .ShowSql()
                    .ProxyFactoryFactory(&quot;NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle&quot;)
                )
                .Mappings(m =&gt; m.FluentMappings.AddFromAssemblyOf&lt;T&gt;()
                                   .ExportTo(Path.GetTempPath())
                )
                ;

I had to add sql-insert, sql-update, sql-delete into XmlHibernateMappingWriter.sorting Dictionary. The reason of applying this patch is: the current sorting xml children logic in FN (r479) has a bug when comparing two many undefined elements.

if (!sorting.ContainsKey(x.Name) || !sorting.ContainsKey(y.Name)) return 0;
e.g.
it was pre-defined that definded-element-A is less than definded-element-B, but if we introduced an un-defined-element-C,
ContainsKey check in sorting children method will say:
definded-element-A == un-defined-element-C,
and
un-defined-element-C ==definded-element-B,
this will result
definded-element-A == definded-element-B!

In my case, all the properties were bubbled up to first level and caused a strange System.Xml.Schema.XmlSchemaValidationException.

Note:  My patch doesn’t support sql-delete-all in array/primitive-array/bag/list/map/set/idbag yet, while they are defined in xsd file.

Fluent NHibernate

The current version of Fluent NHibernate (r479) doesn’t support Sql-insert, sql-update, sql-delete.

FN can do mixed configuration, as example:

Fluently.Configure()
  .Database(configurer)
  .Mappings(m =>
  {
    m.FluentMappings.AddFromAssemblyOf<UserCreator>();
    m.HbmMappings.AddFromAssemblyOf<UserCreator>(); // loads the embedded
resources
  })
  .ExposeConfiguration(BuildSchema)
  .BuildSessionFactory();

But I can’t use this patten to partly mapping properties on the same entity using FN, and use classic hbm to implement SP.

I must work on a horrible legacy db.