How should we query multiple one-to-many relations

Question

Domain

I'm trying to optimize my queries and need some advise. Is this the preferred way of querying one-to-many relations?

My domains looks like this:

MeasureSet

   /// <summary>
    /// 
    /// </summary>
    /// <remarks>
    /// Tables: none
    /// </remarks>
    public class MeasureSet : PersistentEntity
    {
        #region Properties
        /// <summary>
        /// Gets or sets the code.
        /// </summary>
        /// <value>
        /// The code.
        /// </value>
        public virtual string Code { get; set; }
        /// <summary>
        /// Gets or sets the description.
        /// </summary>
        /// <value>
        /// The description.
        /// </value>
        public virtual string Description { get; set; }
        /// <summary>
        /// Gets or sets the measure domains.
        /// </summary>
        /// <value>
        /// The measure domains.
        /// </value>
        public virtual IList<MeasureDomain> MeasureDomains { get; protected set; } 
}

MeasureDomain

   /// <summary>
    /// 
    /// </summary>
    /// <remarks>
    /// Tables: domeinen
    /// </remarks>
    public class MeasureDomain : PersistentEntity
    {
        #region Properties
        /// <summary>
        /// Gets or sets the code.
        /// </summary>
        /// <value>
        /// The code.
        /// </value>
        public virtual string Code { get; set; }
        /// <summary>
        /// Gets or sets the description.
        /// </summary>
        /// <value>
        /// The description.
        /// </value>
        public virtual string Description { get; set; }
        /// <summary>
        /// Gets or sets the explanation.
        /// </summary>
        /// <value>
        /// The explanation.
        /// </value>
        public virtual string Explanation { get; set; }
        /// <summary>
        /// Gets or sets the measure set.
        /// </summary>
        /// <value>
        /// The measure set.
        /// </value>
        public virtual MeasureSet MeasureSet { get; set; }
        /// <summary>
        /// Gets or sets the measure sub domains.
        /// </summary>
        /// <value>
        /// The measure sub domains.
        /// </value>
        public virtual IList<MeasureSubDomain> MeasureSubDomains { get; protected set; }
        /// <summary>
        /// Gets or sets the audits.
        /// </summary>
        /// <value>
        /// The audits.
        /// </value>
        public virtual IList<Audit> Audits { get; protected set; } 
        #endregion
}

MeasureSubDomain

   /// <summary>
    /// 
    /// </summary>
    /// <remarks>
    /// Tables: subdomeinen, subdomeinenbestanden
    /// </remarks>
    public class MeasureSubDomain : PersistentEntity
    {
        #region Properties
        /// <summary>
        /// Gets or sets the code.
        /// </summary>
        /// <value>
        /// The code.
        /// </value>
        public virtual string Code { get; set; }
        /// <summary>
        /// Gets or sets the description.
        /// </summary>
        /// <value>
        /// The description.
        /// </value>
        public virtual string Description { get; set; }
        /// <summary>
        /// Gets or sets the domain.
        /// </summary>
        /// <value>
        /// The domain.
        /// </value>
        public virtual MeasureDomain MeasureDomain { get; set; }
        /// <summary>
        /// Gets or sets the explanation.
        /// </summary>
        /// <value>
        /// The explanation.
        /// </value>
        public virtual string Explanation { get; set; }
        /// <summary>
        /// Gets or sets the files.
        /// </summary>
        /// <value>
        /// The files.
        /// </value>
        public virtual IList<File> Files { get; protected set; }
        /// <summary>
        /// Gets or sets the measure controls.
        /// </summary>
        /// <value>
        /// The measure controls.
        /// </value>
        public virtual IList<MeasureControl> MeasureControls { get; protected set; }
        /// <summary>
        /// Gets or sets the audits.
        /// </summary>
        /// <value>
        /// The audits.
        /// </value>
        public virtual IList<Audit> Audits { get; protected set; }
}

I only need the collection of children, Code and Description properties. The query i'm currently using is this:

one-to-many query

 measureSets = LazySessionFactory.CurrentSession.CreateCriteria<MeasureSet>()
        .Add(Subqueries.PropertyIn("Id",
            DetachedCriteria.For<MeasureDomain>()
                .SetProjection(Projections.Property("MeasureSet.Id"))
                .Add(Subqueries.PropertyIn("Id",
                    DetachedCriteria.For<MeasureSubDomain>()
                    .SetProjection(Projections.Property("MeasureDomain.Id"))
                    .Add(Subqueries.PropertyIn("Id",
                        DetachedCriteria.For<MeasureControl>()
                        .SetProjection(Projections.Property("MeasureSubDomain.Id"))
                        .Add(Subqueries.PropertyIn("Id",
                            DetachedCriteria.For<Measure>()
                            .SetProjection(Projections.Property("MeasureControl.Id"))))))))))
                            .SetCacheable(true)
                            .Future<MeasureSet>().ToList();

Base class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GRCcontrol.Domain.Entities
{
    /// <summary>
    /// Base class for domain entities based on NHibernate.
    /// </summary>
    /// <typeparam name="TId">The type of the id.</typeparam>
    public abstract class PersistentEntity<TId> : IEquatable<PersistentEntity<TId>>
    {
        /// <summary>
        /// Gets or sets the id.
        /// </summary>
        /// <value>
        /// The id.
        /// </value>
        public virtual TId Id { get; protected set; }

        /// <summary>
        /// Determines whether the specified <see cref="System.Object" /> is equal to this instance.
        /// </summary>
        /// <param name="obj">The <see cref="System.Object" /> to compare with this instance.</param>
        /// <returns>
        ///   <c>true</c> if the specified <see cref="System.Object" /> is equal to this instance; otherwise, <c>false</c>.
        /// </returns>
        public override bool Equals(object obj)
        {
            return Equals(obj as PersistentEntity<TId>);
        }
        /// <summary>
        /// Determines whether the specified obj is transient.
        /// </summary>
        /// <param name="obj">The obj.</param>
        /// <returns>
        ///   <c>true</c> if the specified obj is transient; otherwise, <c>false</c>.
        /// </returns>
        private static bool IsTransient(PersistentEntity<TId> obj)
        {
            return obj != null &&
                Equals(obj.Id, default(TId));
        }
        /// <summary>
        /// Gets the type of the unproxied type, since NHibernate's lazy loading technology, creates proxies from entities.
        /// </summary>
        /// <returns></returns>
        private Type GetUnproxiedType()
        {
            return GetType();
        }
        /// <summary>
        /// Equalses the specified other.
        /// </summary>
        /// <param name="other">The other.</param>
        /// <returns></returns>
        public virtual bool Equals(PersistentEntity<TId> other)
        {
            if (other == null)
                return false;
            if (ReferenceEquals(this, other))
                return true;
            if (!IsTransient(this) &&
                !IsTransient(other) &&
                Equals(Id, other.Id))
            {
                var otherType = other.GetUnproxiedType();
                var thisType = GetUnproxiedType();

                return thisType.IsAssignableFrom(otherType) ||
                    otherType.IsAssignableFrom(thisType);
            }

            return false;
        }
        /// <summary>
        /// Returns a hash code for this instance.
        /// </summary>
        /// <returns>
        /// A hash code for this instance, suitable for use in hashing algorithms and data structures like a hash table. 
        /// </returns>
        public override int GetHashCode()
        {
            if (Equals(Id, default(TId)))
                return base.GetHashCode();

            return Id.GetHashCode();
        }
    }
    /// <summary>
    /// Base class for domain entities based on NHibernate.
    /// </summary>
    public abstract class PersistentEntity : PersistentEntity<Guid>
    {
    }
}

I'm still getting all the properties, so also the collection of Audits etc. that i don't really need at this time. Since NHibernate doesn't support projections on collections, i'm wondering how i should change my code for better performance.

Hope you can help me out.

What do you actually want from that query? ie. Do you need the MeasureSet.Code, MeasureSet.Description and MeasureSet.MeasureDomains? And is the point to limit it to only those measuresets that have domains that have subdomains that have controls? — Martin Ernst, Feb 21 '13 at 14:31
@MartinErnst your right. I want the things you said in your comment, but also the MeasureDomain.Code, MeasureDomain.Description, MeasureDomain.MeasureSubDomains and then MeasureSubDomain.Code, etc. I don't need the references to Audits, Files etc. so i don't want to query for them. My query returns these collections also. — Rob Angelier, Feb 21 '13 at 14:35

Martin Ernst · Answer 1 · 2013-02-22T12:11:14.040

0

I think what you really want is eager fetching:

measureSets = LazySessionFactory.CurrentSession.CreateCriteria<MeasureSet>()
    .SetFetchMode("MeasureDomains", FetchType.Eager)
    .SetFetchMode("MeasureDomains.MeasureSubDomains", FetchType.Eager)
    .SetFetchMode("MeasureDomains.MeasureSubDomains.MeasureControls", FetchType.Eager)
    .SetCacheable(true)
    .Future<MeasureSet>().ToList();

Will almost do what you want with the exception that it will not exclude any measuresets that DON'T have domains/subdomains/controls

If this is not good enough, you can restrict it by adding:

measureSets = LazySessionFactory.CurrentSession.CreateCriteria<MeasureSet>()
    .Add(Subqueries.PropertyIn("Id", 
        DetachedCriteria.For<Measure>()
            .CreateAlias("MeasureControl", "mc")
            .CreateAlias("mc.MeasureSubDomain", "msd")
            .CreateAlias("msd.MeasureDomain", "md")
            .SetProjection(Projections.Property("md.MeasureSet.id")))
    .SetFetchMode("MeasureDomains", FetchType.Eager)
    .SetFetchMode("MeasureDomains.MeasureSubDomains", FetchType.Eager)
    .SetFetchMode("MeasureDomains.MeasureSubDomains.MeasureControls", FetchType.Eager)
    .SetCacheable(true)
    .Future<MeasureSet>().ToList();

(I made some assumptions about the rest of your model) This however does have a drawback of requiring a subselect which could hurt performance (YMMV)

edited Feb 22 '13 at 12:11

answered Feb 21 '13 at 15:14

Martin Ernst

5,629
2
17
14

This query does work after my modifications, but the root entity is being added multiple times. I thought, lets add the DistinctRootEntity transformer, but then an exception is thrown: Object does not match target type. Exception occurred getter of GRCcontrol.Domain.Entities.PersistentEntity`1[[System.Guid, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]].Id"} – Rob Angelier Feb 22 '13 at 07:47
Have you implemented Equals (and GetHashCode) on your entities? – Martin Ernst Feb 22 '13 at 09:21
I have updated my question with the base class implementation. – Rob Angelier Feb 22 '13 at 09:32
The exception when you add the result transformer is due to a bug in NHibernate - if you disable caching then it will work – Martin Ernst Feb 22 '13 at 09:35
That problem is indeed solved, but now i'm getting these annoying Lazy exceptions. The first collection of MeasureDomains is loaded correctly but it's breaking on the MeasureSubDomains :S – Rob Angelier Feb 22 '13 at 10:42
Sorry I just saw I had the wrong fetch paths - I have updated the answer with the correct fetch paths – Martin Ernst Feb 22 '13 at 12:12

score 0 · Accepted Answer · answered Feb 26 '13 at 06:34

I spend a couple of hours on this problem, but i finally found a solution. This query is also optimized and needs four calls to the database:

    IEnumerable<MeasureSet> measureSets = null;

    var currentSession = LazySessionFactory.CurrentSession;

    measureSets = currentSession.QueryOver<MeasureSet>().TransformUsing(Transformers.DistinctRootEntity)
        .Fetch(m => m.MeasureDomains).Eager
        .Future();

    var setIds = measureSets.Select(x => x.Id).ToArray();

    var domains = currentSession.QueryOver<MeasureDomain>().TransformUsing(Transformers.DistinctRootEntity)
        .WhereRestrictionOn(x => x.MeasureSet.Id).IsIn(setIds).Fetch(m => m.MeasureSubDomains).Eager.Future();
    var domainIds = domains.Select(x => x.Id).ToArray();

    var subDomains = currentSession.QueryOver<MeasureSubDomain>().TransformUsing(Transformers.DistinctRootEntity)
        .WhereRestrictionOn(x => x.MeasureDomain.Id).IsIn(domainIds).Fetch(m => m.MeasureControls).Eager.Future();
    var subDomainIds = subDomains.Select(x => x.Id).ToArray();

    var controls = currentSession.QueryOver<MeasureControl>().TransformUsing(Transformers.DistinctRootEntity)
        .WhereRestrictionOn(x => x.MeasureSubDomain.Id).IsIn(subDomainIds).Fetch(m => m.Measures).Eager.Future();
    var controlIds = controls.Select(x => x.Id).ToArray();

    currentSession.QueryOver<Measure>().TransformUsing(Transformers.DistinctRootEntity)
        .WhereRestrictionOn(x => x.MeasureControl.Id).IsIn(controlIds).Future();

    return measureSets.AsQueryable();

Please respond to my answer when you have a better solution then this one.

How should we query multiple one-to-many relations

2 Answers2