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