Как мы должны запросить несколько отношений один ко многим

Question

Домен

Я пытаюсь оптимизировать свои запросы и нуждаюсь в некотором совете. Является ли это предпочтительным способом запроса отношений один-ко-многим?

Мои домены выглядят так:

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

Мне нужна только коллекция детей, свойства Code и Description. Запрос, который я сейчас использую, таков:

запрос один ко многим

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

Базовый класс

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>
    {
    }
}

Я все еще получаю все свойства, а также коллекцию аудитов и т. Д., Которые мне сейчас не нужны. Поскольку NHibernate не поддерживает проекции на коллекции, мне интересно, как мне изменить свой код для повышения производительности.

Надеюсь, ты сможешь мне помочь.

Answer 1

Я трачу пару часов на эту проблему, но наконец-то нашел решение. Этот запрос также оптимизирован и требует четырех обращений к базе данных:

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

Пожалуйста, ответьте на мой ответ, когда у вас есть лучшее решение, чем это.

Answer 2

Я думаю, что вы действительно хотите, чтобы получить:

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

Почти будет делать то, что вы хотите, за исключением того, что он не исключает наборы показателей, которые не имеют доменов / поддоменов / элементов управления

Если этого недостаточно, вы можете ограничить его, добавив:

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

(Я сделал некоторые предположения об остальной части вашей модели) Однако у этого есть недостаток, заключающийся в том, что требуется выборка, которая может снизить производительность (YMMV)