Warning: mysqli_num_fields() expects parameter 1 to be mysqli_result, boolean given in /www/wwwroot/dev.zhalaotie.com/wp-includes/wp-db.php on line 3215

Warning: mysqli_num_fields() expects parameter 1 to be mysqli_result, boolean given in /www/wwwroot/dev.zhalaotie.com/wp-includes/wp-db.php on line 3215
c# – Convert to NHibernate Queryover – Stack Overflow – 扎老铁
Warning: mysqli_num_fields() expects parameter 1 to be mysqli_result, boolean given in /www/wwwroot/dev.zhalaotie.com/wp-includes/wp-db.php on line 3215

Warning: mysqli_num_fields() expects parameter 1 to be mysqli_result, boolean given in /www/wwwroot/dev.zhalaotie.com/wp-includes/wp-db.php on line 3215
Warning: mysqli_num_fields() expects parameter 1 to be mysqli_result, boolean given in /www/wwwroot/dev.zhalaotie.com/wp-includes/wp-db.php on line 3215
class="post-52015 post type-post status-publish format-standard has-post-thumbnail hentry">

c# – Convert to NHibernate Queryover – Stack Overflow

How do I convert following SQL to NH Queryover,

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

asked Aug 24 ’11 at 6:42

What have you tried so far? – Rippo Aug 24 ’11 at 7:51
Actually this is more simplified version of the query I want, the basic idea is to get the row count of a query with multiple groupings to use it for paging. I have no idea how to get the inner query part to work, that is why this question is added. – Low Flying Pelican Aug 24 ’11 at 8:10

2 Answers

active
oldest
votes

I’ve tried to solve it using QueryOver and Alias; sorry, but I can’t test this code now. 🙁

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

I hope it’s helpful!

answered Aug 24 ’11 at 8:15

Faber
9751521

Thank you for the answer, The SQL generated by this does not have the Group By section, It give the total row count which is different from the no of rows returned with group by clause. – Low Flying Pelican Aug 24 ’11 at 8:27
@Adipa Gunasekara: Ok, try to use .Select(Projections.Group(() => myProd.CODE), Projections.Group(() => myLocation.BATCHID)). I’m sorry but I can’t try it again 🙁 – Faber Aug 24 ’11 at 8:31
It also gives the same results without group by section, I have tried this with quite a lot of different combinations but as soon as I try to get the rowCount the group by clause is goes missing in the query, so I believe only way to do this is to execute query as inner query as I have suggested in the question, still I do not have clear idea if it’s even possible with NH Queryover. – Low Flying Pelican Aug 24 ’11 at 8:44

up vote
1
down vote

accepted

I have managed to achieve this using a custom projection, if anyone interested code is as follows,

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

here, the constructor of the projection needs to be passed with all the group by projections like,

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

answered Aug 25 ’11 at 3:50

Your Answer

 

Sign up or login

Sign up using Google

Sign up using Facebook

Sign up using Stack Exchange

Post as a guest

Name

Email

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you’re looking for?
Browse other questions tagged
or ask your own question.

发表评论

电子邮件地址不会被公开。