I have a query here:
var netSales = uow.Session
.QueryOver<TransactionSummary>(() => tsAlias)
.Where(x => x.EntityId == entityId)
.And(x => x.TransactionDate >= dayOpen)
.And(x => x.TransactionDate < dayClose)
.SelectList(x => x
.SelectSum(xx => xx.SalesExclGstExcl).WithAlias(()
=> tsAlias.SalesExclGstExcl)
.SelectSum(xx => xx.TransactionCount).WithAlias(()
=> tsAlias.TransactionCount))
.TransformUsing(Transformers.AliasToBean(typeof(TransactionSummary)))
.Take(1)
.FutureValue();
This generates the following SQL
SELECT TOP (1 /* @p0 */) sum(this_.Sales_Excl_GST_EXCL) as y0_,
sum(this_.TransactionCount) as y1_
FROM dbo.tbTransactionSummary this_
WHERE this_.EntityId = 678 /* @p1 */
and this_.TransactionDate >= '2010-10-06T05:00:00.00' /* @p2 */
and this_.TransactionDate < '2010-10-07T05:00:00.00' /* @p3 */;
Which works perfectly. No issues.
------------------------
The problem is that if I'm looking at a date range that has no data,
it returns NULL and breaks. So far so good...
If I add a Coalesce onto the Summed projections:
.SelectList(x => x
.SelectSum(xx => xx.SalesExclGstExcl.Coalesce(0m)).WithAlias(() =>
tsAlias.SalesExclGstExcl)
.SelectSum(xx => xx.TransactionCount.Coalesce(0)).WithAlias(() =>
tsAlias.TransactionCount))
This also generates the correct SQL:
SELECT TOP (1 /* @p0 */) sum(coalesce(this_.Sales_Excl_GST_EXCL, 0 /*
@p1 */)) as y0_,
sum(coalesce(this_.TransactionCount, 0 /* @p2 */))
as y2_
FROM dbo.tbTransactionSummary this_
WHERE this_.EntityId = 678 /* @p3 */
and this_.TransactionDate >= '2010-10-06T05:00:00.00' /* @p4 */
and this_.TransactionDate < '2010-10-07T05:00:00.00' /* @p5 */
Except if you look at the returned columns, the second one is named
'y2_' instead of 'y1_'
So when the result gets back to NH it throws an exception:
could not execute query
[ SELECT TOP (@p0) sum(coalesce(this_.Sales_Excl_GST_EXCL, @p1)) as
y0_, sum(coalesce(this_.TransactionCount, @p2)) as y2_ FROM
dbo.tbTransactionSummary this_ WHERE this_.EntityId = @p3 and
this_.TransactionDate >= @p4 and this_.TransactionDate < @p5 ]
Name:cp0 - Value:0 Name:cp1 - Value:0 Name:cp2 - Value:678
Name:cp3 - Value:6/10/2010 5:00:00 AM Name:cp4 - Value:7/10/2010
5:00:00 AM
[SQL: SELECT TOP (@p0) sum(coalesce(this_.Sales_Excl_GST_EXCL, @p1))
as y0_, sum(coalesce(this_.TransactionCount, @p2)) as y2_ FROM
dbo.tbTransactionSummary this_ WHERE this_.EntityId = @p3 and
this_.TransactionDate >= @p4 and this_.TransactionDate < @p5]
Is this a possible bug in NH? Or am I doing something wrong :(
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
