NHibernate QueryOver 与分页同时选择子查询的前 1 个

Question

我有 2 个实体（这些实体被分解为更简单的问题）：

实体 A

    public class EntityA
        {
            protected IList<EntityB> _bList = new List<EntityB>();

            virtual public int Id { get; set; }
            virtual public int ExtId { get; set; }


            public virtual void AddB(EntityB b)
            {
                if (!_bList.Contains(b)) _bList.Add(b);
                b.A = this;
                b.ExtId  = this.ExtId;
            }

            public virtual void RemoveB(EntityB b)
            {
                _bList.Remove(b);
            }

            public virtual IList<EntityB> BList
            {
                get { return _bList.ToList().AsReadOnly(); }
            }
        }

实体 A 映射

    <?xml version="1.0" encoding="utf-8" ?>
    <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true">
      <class name="hibernate.domain.mappings.EntityA, hibernate.domain" lazy="true">
        <id name="Id">
          <generator class="native" />
        </id>
        <property type="int" name="ExtId" column="[ExtId]" />
        <bag
          name="BList"
          table="EntityB"
          cascade="all"
          lazy="true"
          inverse="true"
          access="field.camelcase-underscore"
          optimistic-lock="false"
          >
          <key column ="ExtId" property-ref="ExtId" />
          <one-to-many class="hibernate.domain.mappings.EntityB, hibernate.domain" />
        </bag>
    </hibernate-mapping>

实体 B

     public class EntityB
        {
            protected EntityA _a;

            virtual public int Id { get; set; }
            virtual public int ExtId { get; set; }
            virtual public EntityA A
            {
                get { return _a; }
                set { _a = value; }
            }
        }

实体 B 映射

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true">
  <class name="hibernate.domain.mappings.EntityB, hibernate.domain" lazy="true">
    <id name="Id">
      <generator class="native" />
    </id>
    <property type="int" name="ExtId" column="[EXTID]" />
    <many-to-one
            name = "A"
      property-ref ="ExtId"
            not-null="true"
            class = "hibernate.domain.mappings.EntityA, hibernate.domain"
      access="field.camelcase-underscore"
            cascade = "save-update"
            fetch="select"
            insert = "false"
      lazy = "false"
            update = "false"
      column="ExtId"
      />
  </class>
</hibernate-mapping>

我需要做的是使用 Queryover 来获取带有分页的 A 列表，同时选择 B 的第一项与 A 相关，

我使用了以下查询，

    using (ISession session = SessionProvider.OpenSession())
                {
                    var bOver = (QueryOver<EntityB, EntityB>)session.QueryOver(() => bAlias)
                        .JoinAlias(() => bAlias.A, () => aAlias)
                        .SelectList(b => b.Select(() => bAlias.Id))
                        .Take(1);

                    var aOver = session.QueryOver(() => aAlias)
                        .SelectList(l => l.Select(() => aAlias.Id)
                        .SelectSubQuery<EntityB>(bOver));



var result = aOver.Skip(1).Take(1).List<object[]>();
            }

但生成的查询就像，以下

SELECT TOP (10) y0_,
                (SELECT TOP (10) this_0_.id AS y0_
                 FROM   (SELECT this_.id
                                AS y0_,
                                (SELECT TOP (1) this_0_.id
                                                AS
                                                y0_,
                                                Row_number() OVER(ORDER BY
                                                current_timestamp) AS
                                                __hibernate_sort_row
                                 FROM   entityb this_0_
                                        INNER JOIN entitya aalias1_
                                          ON
this_0_.extid = aalias1_.[EXTID])
AS y1_
FROM   entitya this_) AS QUERY
WHERE  QUERY.__hibernate_sort_row > 1
ORDER  BY QUERY.__hibernate_sort_row)  

并且它不接近正确，那么我如何解决这种情况（在现实世界中，我需要选择多个第一个项目，如 B ）一个）

Answer 1

好的，我已经成功地通过对 Nhibernate 投影进行一些修改（实际上添加了一个自定义投影）来解决了这种情况

[Serializable]
    public class TopRowProjection : SimpleProjection
    {
        private PropertyProjection _projection;

        public TopRowProjection(PropertyProjection projection)
        {
            _projection = projection;
        }

        public override bool IsAggregate
        {
            get { return true; }
        }

        public override IType[] GetTypes(ICriteria criteria, ICriteriaQuery criteriaQuery)
        {
            return _projection.GetTypes(criteria, criteriaQuery);
        }

        public override SqlString ToSqlString(ICriteria criteria, int position, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
        {
            SqlStringBuilder result = new SqlStringBuilder().Add(" top(1) ");
            result.Add(_projection.ToSqlString(criteria, position, criteriaQuery, enabledFilters));
            result.Add(" ");
            return result.ToSqlString();
        }

        public override string ToString()
        {
            return "select top(1)";
        }

        public override bool IsGrouped
        {
            get { return false; }
        }

        public override SqlString ToGroupSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery,
                                                   IDictionary<string, IFilter> enabledFilters)
        {
            throw new InvalidOperationException("not a grouping projection");
        }
    }

，代码修改如下，一切顺利

using (ISession session = SessionProvider.OpenSession())
            {
                var bOver = (QueryOver<EntityB, EntityB>)session.QueryOver(() => bAlias)
                    .JoinAlias(() => bAlias.A, () => aAlias)
                    .Select(new TopRowProjection(Projections.Property(() => bAlias.Id)));

                var aOver = session.QueryOver(() => aAlias)
                    .SelectList(l => l.Select(() => aAlias.Id)
                    .SelectSubQuery<EntityB>(bOver));

                var result = aOver.Skip(1).Take(1).List<object[]>();
            }