转换为NHibernate查询

本文关键字：查询 NHibernate 转换 | 更新日期: 2023-09-27 18:06:25

如何将以下SQL转换为NH查询，

select COUNT(*)
from
(select p.CODE,sl.BATCH from STORELOCATION sl
right join PRODUCT p on p.CODE = sl.CODE
group by p.CODE,sl.BATCHID)
as t

转换为NHibernate查询

我试图解决它使用QueryOver和别名;对不起，我现在不能测试这个代码。(

        ProductModel myProd = null;
        StoreLocationModel myLocation = null;
        var qOver = _HibSession.QueryOver<ProductModel>(() => myProd)
            .JoinAlias(() => myProd.Locations, () => myLocation, JoinType.LeftOuterJoin)
            .Select(Projections.GroupProperty(myProd.CODE), Projections.GroupProperty(myLocation.BATCHID))
            .RowCount();

我希望这是有帮助的!

我已经设法使用自定义投影实现了这一点，如果有人感兴趣的代码如下，

[Serializable]
    public class GroupCountProjection : SimpleProjection
    {
        private PropertyProjection[] _projections;
        public GroupCountProjection(PropertyProjection[] projections)
        {
            _projections = projections;
        }
        public override bool IsAggregate
        {
            get { return true; }
        }
        public override IType[] GetTypes(ICriteria criteria, ICriteriaQuery criteriaQuery)
        {
            return new IType[] { NHibernateUtil.Int32 };
        }
        public override SqlString ToSqlString(ICriteria criteria, int position, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
        {
            SqlStringBuilder result = new SqlStringBuilder()
                .Add(" count(*) as y")
                .Add(position.ToString())
                .Add("_ from ( select ");
            for (int index = 0; index < _projections.Length; index++)
            {
                PropertyProjection projection = _projections[index];
                if (index > 0)
                    result.Add(",");
                result.Add(projection.ToSqlString(criteria, ++position, criteriaQuery, enabledFilters));
            }
            result.Add(" ");
            return result.ToSqlString();
        }
        public override string ToString()
        {
            return "select count(*)";
        }
        public override bool IsGrouped
        {
            get { return true; }
        }
        public override SqlString ToGroupSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery,
                                                   IDictionary<string, IFilter> enabledFilters)
        {
            SqlStringBuilder result = new SqlStringBuilder();
            for (int index = 0; index < _projections.Length; index++)
            {
                PropertyProjection projection = _projections[index];
                if (index > 0)
                    result.Add(",");
                result.Add(StringHelper.RemoveAsAliasesFromSql(projection.ToSqlString(criteria, 0, criteriaQuery,enabledFilters)));
            }
            result.Add(") as tbly");
            return result.ToSqlString();
        }
    }
这里的

，投影的构造函数需要与所有group by投影一起传递，例如

var countQuery = GetProductQuery(); // this is the queryover 
            countQuery
                .Select(new GroupCountProjection(new[]{
                    Projections.Group(() => _productAlias.Code),
                    Projections.Group(() => _storeLocationAlias.Batch),
                 }));
int resultCount = (int)countQuery.List<object>().SingleOrDefault();
相关文章：