I have a query that I can run in LinqPad, but not in NHibernate LINQ. I found a similar bug on NHibernate Jira NHibernate NH-2865, but I think this perhaps a different bug and I'm looking for possible alternatives.
The query that works in LinqPad using default LINQ to SQL is like this:
from ticket in LotteryTickets
group tiket by ticket.ticketType into g
select new
{
TicketType = g.Key,
TotalWinningTickets = g.Count(b => b.WinAmount != 0),
TotalWon = g.Sum(b => b.WinAmount * b.ticketWeight),
TotalTickets = g.Count(),
}
There are various other ways to count the TotalWinningTickets such as TotalWinningTickets = g.Sum(t => t.WinAmount > 0 ? 1 : 0)
or g.Sum(t => t.WinAmount == 0 ? 0 : 1)
Now, with NHibernate LINQ I have been unable to get this working without changing my underlying db table. My alternative was to add a table column called HasWon and I set it to 0 for false and 1 for true. This does work, but I'm left feeling there is a better way other than modifying the db table. Perhaps a subselect using a calculated field. My NHibernate LINQ ended up looking like this:
from ticket in session.Query<Ticket>()
group ticket by ticket.TicketType into g
select new ReportRow
{
TicketType = g.Key,
TotalWinningBets = g.Sum(t => t.HasWon),
TotalTickets = g.Count(),
TotalWon = g.Sum(t => t.WinAmount * t.WagerWeight)
};
What I'm effectively trying to do is multiple Select COUNT in my NHibernate query, but based on different criteria. The strangest this is that NHibernate converts all my Count(t => t.???)
incorrectly into COUNT(*)
in the generated SQL.
LinqPad generates SQL that works this way: SELECT SUM( (CASE WHEN [t1].[WinAmount] > @p3 THEN @p4 ELSE @p5 END)) AS [value]
Hoping there is a better way.