-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Select Count on a Conditional Projection
PostPosted: Thu May 05, 2011 7:01 am 
Newbie

Joined: Thu May 13, 2010 7:55 am
Posts: 2
Hi there,

I'm having issues using a conditional projection within a Projections.Count.
To explain better, here's the SQL statement i'm trying to achieve:

Code:
SELECT
COUNT(DISTINCT(case when a.Property = 1 then a.Id else null end)) as MyCount
FROM MyTable as a


and here's the code i'm trying to use (with QueryOver):

Code:

var query = Session.QueryOver<MyEntity>();

var restriction = Restrictions.Where<MyEntity>(x => x.Property == 1);
var conditionalProjection = Projections.Conditional(restriction, Projections.Property(x => x.Id), Projections.Constant(0));

query.SelectList(list => list
                                         .Select(Projections.Count(Projections.Distinct(conditionalProjection))))



Right. To state before hand , this is working perfectly fine if I use Projections.Sum instead of Projections.Count,
the exception that I got from this code is the following one:

Code:
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

[ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index]
   System.Collections.Generic.List`1.get_Item(Int32 index) +12729020
   NHibernate.Engine.QueryParameters.SetParameterLocation(IList`1 sqlParameters, Int32 parameterIndex, Int32 sqlLocation, Int32 span) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Engine\QueryParameters.cs:423
   NHibernate.Engine.QueryParameters.PrepareParameterTypes(SqlString sqlString, ISessionFactoryImplementor factory, GetNamedParameterLocations getNamedParameterLocations, Int32 startParameterIndex, Boolean addLimit, Boolean addOffset) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Engine\QueryParameters.cs:463
   NHibernate.Loader.Loader.PrepareQueryCommand(QueryParameters queryParameters, Boolean scroll, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1126
   NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:411
   NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:243
   NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1694



and if I look at the stack trace there's the SQL generated correctly. So it seems that there is something wrong setting the SQL parameters when building up the query.
I probably don't remember well but i heard about a problem with positional parameters used by the Conditional Projections and the Named parameters used by the Count...
is it possible that it's some kind of NH problem ? or I am doing bad/wrong stuff ? :)

Last thing is: as you can notice i'm using Projections.Constant(0) as the false part of the conditional projection, in the sql instead i'd like to have the false parte of the condition as null. This is actually another problem and i'm trying to find a solution for this as well but it's absolutely not affecting the main problem described here. It will just generate "else 0" instead of "else null" in the sql returning a wrong count, but again this is completely another issue.

If anyone has any suggestions i will really appreciate :)

Thanks a lot

Cheers

Alessandro


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.