Jan Blaha

Blog about software development

NHibernate QueryOver duplicated results filtering

.NET NHibernate

Problem

NHibernate has very powerful syntax for doing sql queries. It's QueryOver syntax is the biggest reason for using NHibernate instead of Entity Framework for me. It's dificult to start with it but once you master it you can do almost every query. We have hundreds of very complicated queries in our application and still using only QueryOver without HQL. The problem is with power comes also complexity and issues. Issue we were facing with QueryOver was duplications in the result entity list. This was happening when the query contained joins on properties with many-to-many relationships. The generated sql statement was all right but parsing the result set form db was producing duplications.

Solution

Luckily NHibernate power lays in various extension points it provides. One of them is the hook for transforming entity tree just after parsing them from db result set. This hook can be added using TransformUsing method. This method accepts IResultTransformer interface which is offering hooks to entity transformation process. So we will do the hard job for nhibernate and crawl entity tree and remove all duplications. We need avoid stepping into properties that are no fetched yet. It is also important to clear nhibernate dirty flag on collections otherwise it will try to sync it back with the db.

Here is final code we use:

public class DistinctIdTransformer : IResultTransformer
{
    public object TransformTuple(object[] tuple, string[] aliases)
    {
        return tuple.Last();
    }

    public IList TransformList(IList list)
    {
        if (list.Count == 0)
            return list;
        var result = (IList)Activator.CreateInstance(list.GetType());
        var distinctSet = new HashSet<EntityBase>();
        foreach (object item in list)
        {
            var entity = item as EntityBase;
            if (entity == null)
                continue;
            if (distinctSet.Add(entity))
            {
                result.Add(item);
                HandleItemDetails(item, new HashSet<EntityBase>());
            }
        }
        return result;
    }

    private void HandleItemDetails(object item, HashSet<EntityBase> processed)
    {
        var entity = item as EntityBase;
        if (!processed.Add(entity) || item == null)
            return;

        foreach (PropertyInfo property in item.GetType().GetProperties())
        {
            if (ImplementsBaseType(property.PropertyType))
            {
                HandleCollection(item, processed, property);
                continue;
            }
        }
    }

    private void HandleCollection(object item, HashSet<EntityBase> processed, PropertyInfo property)
    {
        dynamic detailList = property.GetValue(item, null);

        //avoid lazy loading here
        if (!NHibernateUtil.IsInitialized(detailList))
            return;

        if (detailList != null)
        {
            var distinct = new HashSet<EntityBase>();
            var isDirty = false;
            foreach (var subItem in detailList)
            {
                var subEntity = subItem as EntityBase;
                if (distinct.Add(subEntity))
                {
                    HandleItemDetails(subEntity, processed);
                }
                else
                {
                    isDirty = true;
                }
            }

            if (!isDirty)
                return;

            detailList.Clear();

            foreach (var subItem in distinct)
            {
                detailList.Add(subItem);
            }

            //this is important to tell nhibernate it should not generate any udates
            detailList.ClearDirty();
        }
    }

    public static bool ImplementsBaseType(Type t)
    {
        int found = (from i in t.GetInterfaces()
                     where i.IsGenericType &&
                           i.GetGenericTypeDefinition() == typeof(IEnumerable<>) &&
                           typeof(EntityBase).IsAssignableFrom(i.GetGenericArguments()[0])
                     select i).Count();
            return (found > 0);
    }
}

Now you can add DistinctIdTransformer to QueryOver and hopefully you will not get duplications anymore.

 Session.QueryOver<Contact>(() => alias).TransformUsing(new DistinctIdTransformer());

last blog posts


09-12-2017 18:40 jsreport

Quite some time ago I blogged about rendering pdf reports in c#. Recently we have added excel reports into jsreport and it was released with a little delay also into .NET. This means you should be able to use both html-to-xlsx and xlsx recipes to create excel files from your .NET environments now.

read more

09-10-2015 14:09 AWS

Such a very common thing like adding an existing external volume to Amazon elastic beanstalk is not easily supported out of the box. The official blog mentions only how to attach a snapshot or how to attach and overwrite a new volume every time the service starts. It took me a while to make the config file actually adding an existing volume without formatting it every time so I share it here with you...

read more

04-10-2015 14:09 jsreport

The best practice when adding email notifications feature to your system is to separate as much as you can from email body assembling to email sending outside of the core system. The emails templates quite likely often changes and you don't want to deploy the system because of every single notification change. The best is to just separate everything into an external system and give the access to your PR or Marketing department so they change emails as the time goes without affecting the core system.

read more

Jan Blaha

About author

Hi! My name is Jan Blaha. I'm software developer and startup enthusiastic. See my current work.