Lookup in db or in code (enum)?

Maybe this is a solved problem already, but I couldn’t figure it out until today. With massive usage of NHibernate, lookup in db or in code always appear as a puzzle to me. For example, given a lookup dictionary like mime type: text/plain, text/html, image/gif and other stuff. It make sense to save all those data into a table then enable foreign key on all referenced tables. But, in code, those mime type id itself will appear like magic number, I don’t likes this.

So, creating an enum contains all items solve the magic number issue, partly, and introduce another sync concern, what if data in db and enum in code out of sync?

This post demos a brilliant idea of creating enum on the fly, but mime value has slash in it, this solution doesn’t work. Also, wrapper class around enum seems unnecessary.

Based on the suggestion from this post. here comes my solution,


   public enum MimeType
    {
        [Description("application/octet-stream")] ApplicationOctetStream,
        [Description("text/plain")] TextPlain = 1,
        [Description("text/html")] TextHtml,
        [Description("application/pdf")] ApplicationPdf,
        [Description("application/vnd.ms-excel")] ApplicationVndMsExcel,
        [Description("image/gif")] ImageGif,
        [Description("image/jpeg")] ImageJpeg,
        [Description("application/rtf")] ApplicationRtf,
        [Description("application/zip")] ApplicationZip,
        [Description("application/msword")] ApplicationMsword,
        [Description("application/mspowerpoint")] ApplicationMspowerpoint
    }

    /// <summary>
    /// Extension methods container for enum used to check synchronization between enum in code and data in db.
    /// see usage in should_fetch_all_mime_types() of EmailQueueRepositoryTest class
    /// </summary>
    public static class MimeTypeEx
    {
        public static MimeType ToMimeTypeValue(this string value)
        {
            foreach (FieldInfo fi in typeof (MimeType).GetFields())
            {
                if (!fi.IsStatic)
                {
                    continue;
                }

                object[] attrs = fi.GetCustomAttributes(typeof (DescriptionAttribute), false);
                if (attrs == null || attrs.Length <= 0)
                {
                    continue;
                }

                var descr = (DescriptionAttribute) attrs[0];
                if (0 == string.Compare(value.Trim(), descr.Description))
                    // in case varchar type. trim it before compare.
                {
                    return (MimeType) fi.GetValue(null);
                }
            }

            throw new InvalidDataException(value);
            //return MimeType.Unknown;
        }

        public static string ToMimeTypeString(this MimeType value)
        {
            foreach (FieldInfo fi in typeof (MimeType).GetFields())
            {
                if (!fi.IsStatic || (MimeType) fi.GetValue(null) != value)
                {
                    continue;
                }

                object[] attrs = fi.GetCustomAttributes(typeof (DescriptionAttribute), false);
                if (attrs == null || attrs.Length <= 0)
                {
                    continue;
                }

                var descr = (DescriptionAttribute) attrs[0];
                return descr.Description;
            }

            return string.Empty;
        }
    }

    /// <summary>
    /// Used to read mime type from db, then we can check synchronization with defined enum mime type.
    /// </summary>
    public class MimeTypeInDb
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual bool BinaryInd { get; set; }
    }

Test code, similar functionality should be done in app start or somewhere static ctor to ensure the synchronization.

       [Test]
        public void should_fetch_all_mime_types()
        {
            var results = _repository.FetchAll<MimeTypeInDb>();

            Assert.That(results.Count(), Is.GreaterThan(0));

            foreach (MimeTypeInDb mimeTypeInDb in results)
            {
                Console.WriteLine(mimeTypeInDb.Name.ToMimeTypeValue()); // will throw invalid data exception if enum not found.
            }

            foreach (MimeType mimeType in Enum.GetValues(typeof(MimeType)))
            {
                Console.WriteLine(mimeType.ToMimeTypeString());
                if (! results.Any(x => x.Name.Trim() == mimeType.ToMimeTypeString()))
                {
                    Assert.Fail(mimeType.ToMimeTypeString() +" not found in db.");
                }
            }
        }

About mapping the enum to int in FluentNHibernate:

  Map(x => x.MimeType).Column("mime_type_id").CustomType<MimeType>();

Note: using CustomType<int>() also works, but it will cause extra update problem for reading query. NH is trying to set column to enum string value.

Advertisements

NHibernate Session.Query ignore Fetch Join?

We set up child collection relationship to Fetch as Join in mapping, using fluentNHibernate:

HasMany( x => Details ).Fetch.Join();

It works fine at least for Session.Get<T>(id), we can see only one joined query instead of two separated ones.

But it seems  Session.Query<T>() keeps ignoring this join fetch setting, we always got two separated selects when using Session.Query<T>. A Bug?

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.

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!

Queryable Repository

Say we have a Post domain class, with properties like Header, details, and PostDate etc.

We want do many different kind of query/search from task/service layer, but we don’t have to change both task and repository for every new type of search requirement.

It would be nice if PostRepository can take the predict to do query.

        public IQueryable<Post> SearchBy( Expression<Func<Post, bool>> predict)
        {
             return Session.Query<Post>().Where(predict);
        }

For unit test like this:

        [Test]
        public void should_do_search_for_keyword_and_date_range()
        {
            var startDate = new DateTime(2012, 1, 1);
            var endDate = new DateTime(2011, 1, 1);
            var results = _repository.SearchBy(post => post.Header.Contains("asdf") && post.PostDate >= startDate && post.PostDate <= endDate);
            Assert.That(results, Is.Empty);
        }

The generated sql:

NHibernate: select post0_.Post_ID as Post1_0_, post0_.Header as Header0_, post0_.Sub_Header as Sub3_0_, post0_.Description as Descript4_0_, post0_.Is_News_Clip as Is5_0_, post0_.Image_Name as Image6_0_, post0_.More_Url as More7_0_, post0_.Post_Date as Post8_0_, post0_.S_Date as S9_0_, post0_.C_Date as C10_0_, post0_.SU_ID as SU11_0_, post0_.CU_ID as CU12_0_ from Post_TAB post0_ where (post0_.Header like ('%'+@p0+'%')) and post0_.Post_Date>=@p1 and post0_.Post_Date<=@p2;@p0 = 'asdf' [Type: String (4000)], @p1 = 2012-01-01 12:00:00 AM [Type: DateTime (0)], @p2 = 2011-01-01 12:00:00 AM [Type: DateTime (0)]

The the task/service can play criteria in whatever way they want, while the repository won’t care.

In fact, the queryable itself is very flexible, we can just create a Query method in repository class to remove the predict expression from method:

        public IQueryable<Post> Query()
        {
            return Session.Query<Post>();
        }
public IEnumerable<PostedItemDto> FindPostsByCriteria(PostCriteria criteria)
 {
 IQueryable<Post> results = _postRepository.Query();

 if (!(string.IsNullOrEmpty(criteria.Keyword)))
 results = results.Where(post =>
 post.Header.Contains(criteria.Keyword)
 || post.SubHeader.Contains(criteria.Keyword)
 || post.Description.Contains(criteria.Keyword))
 ;

 if ( criteria.FromDate != null ) results = results.Where(post => post.PostDate >= criteria.FromDate);

 if ( criteria.ToDate != null ) results = results.Where(post => post.PostDate <= criteria.ToDate);

 return new PostTranslator().Map(results.ToList());
 }

Unfortunately, QueryOver in new NH can not do this yet. To achieve the same result, repository has to write in this way:

            return Session.QueryOver<Post>()
                .WhereRestrictionOn(post => post.Header).IsLike("%asdk%")
                .WhereRestrictionOn(post => post.PostDate).IsBetween(fromDate).And(endDate)
                .List();

It looks clear, but we have to maintain it for criteria structure changes.

Even LinQ is not strong enough as this post indicated, but it has it’s advantage.

NHibernate Subquery

For example, to get the max id:

Classic way:

Session.CreateCriteria(typeof(T))
 .SetProjection(Projections.Max("Id"))
 .UniqueResult()

Linq2Nhibernate seems doesn’t does support sub-query, while doing Session.Linq<Email>().Max(x => x.Id) generated max in sql query, but I got null ref exception when using generic in my base repository syntax like  Linq<T>().Max(x => x.Id),    I was using Linq2NHibernate version 1.1.0.1001.

System.NullReferenceException : Object reference not set to an instance of an object.
at NHibernate.Loader.Criteria.CriteriaQueryTranslator.GetEntityName(ICriteria subcriteriaString propertyName)
at NHibernate.Loader.Criteria.CriteriaQueryTranslator.GetType(ICriteria subcriteriaString propertyName)
at NHibernate.Criterion.AggregateProjection.GetTypes(ICriteria criteriaICriteriaQuery criteriaQuery)
at NHibernate.Loader.Criteria.CriteriaQueryTranslator.get_ProjectedTypes()
at NHibernate.Loader.Criteria.CriteriaJoinWalker..ctor(IOuterJoinLoadable persisterCriteriaQueryTranslator translatorISessionFactoryImplementor factoryICriteria criteriaString rootEntityNameIDictionary`2 enabledFilters)
at NHibernate.Loader.Criteria.CriteriaLoader..ctor(IOuterJoinLoadable persisterISessionFactoryImplementor factoryCriteriaImpl rootCriteriaString rootEntityNameIDictionary`2 enabledFilters)
at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteriaIList results)
at NHibernate.Impl.CriteriaImpl.List(IList results)
at NHibernate.Impl.CriteriaImpl.UniqueResult()
at NHibernate.Linq.Visitors.ImmediateResultsVisitor`1.HandleAggregateCall(MethodCallExpression call) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\ImmediateResultsVisitor.cs: line 153
at NHibernate.Linq.Visitors.ImmediateResultsVisitor`1.VisitMethodCall(MethodCallExpression call) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\ImmediateResultsVisitor.cs: line 65
at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression exp) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\ExpressionVisitor.cs: line 70
at NHibernate.Linq.Visitors.NHibernateExpressionVisitor.Visit(Expression exp) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\NHibernateExpressionVisitor.cs: line 29
at NHibernate.Linq.Visitors.ImmediateResultsVisitor`1.GetResults(MethodCallExpression expr) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\ImmediateResultsVisitor.cs: line 35
at NHibernate.Linq.Visitors.RootVisitor.HandleImmediateResultsCall(MethodCallExpression call) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\RootVisitor.cs: line 202
at NHibernate.Linq.Visitors.RootVisitor.VisitMethodCall(MethodCallExpression expr) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\RootVisitor.cs: line 92
at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression exp) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\ExpressionVisitor.cs: line 70
at NHibernate.Linq.Visitors.NHibernateExpressionVisitor.Visit(Expression exp) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\NHibernateExpressionVisitor.cs: line 29
at NHibernate.Linq.Visitors.NHibernateQueryTranslator.TranslateInternal(Expression expression) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\NHibernateQueryTranslator.cs: line 48
at NHibernate.Linq.Visitors.NHibernateQueryTranslator.Translate(Expression expressionQueryOptions queryOptions) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\NHibernateQueryTranslator.cs: line 32
at NHibernate.Linq.NHibernateQueryProvider.TranslateExpression(Expression expression) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\NHibernateQueryProvider.cs: line 56
at NHibernate.Linq.NHibernateQueryProvider.Execute(Expression expression) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\NHibernateQueryProvider.cs: line 63
at NHibernate.Linq.QueryProvider.System.Linq.IQueryProvider.Execute(Expression expression) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\QueryProvider.cs: line 29
at System.Linq.Queryable.Max(IQueryable`1 sourceExpression`1 selector)

It might be a bug in linq2NH or in my base Repository, Linq2NH is deprecated anyway.

In NHibernate 3.0, we can take advantage of the new built-in Linq provider in NH: QueryOver syntax.

 session.QueryOver<Customer>()
.SelectList(list => list.SelectMax(c => c.Id))
.List<int>().FirstOrDefault();
 

Update: I thought using Session.Query<T>.Max(x => x.Id) will only do the in-memory collection max operation, in fact it does generate the max query over database. Thanks to Phillip Haydon for pointing out.