LINQ NHibernate- Why do I get Column 'is invalid in the HAVING clause?

Question

I want to get the last entries in a table and join them with another table to produce entries which have a mismatch.

I get the following error

Column is invalid in the HAVING clause 
because it is not contained in either 
an aggregate function or the GROUP BY clause

for both CFWD and Balance when running the following

var lastEntries = from s in session.Query<Statement>()
                  group s by s.Customer.Id into grp
                  select grp.OrderByDescending(g => g.Id).First();

 var customers = session.Query<Customer>();

 var balances = from s in lastEntries 
                join c in customers on s.Customer.Id equals c.Id 
                where s.Customer.Balance !=  s.CFwd
                select s ;

 foreach (var line in balances )
 {
     ...
 }

However, testing the same in LiNQPad produces the expected results without errors

var lastEntries = from s in Statements
                  group s by s.Customer.Id into grp
                  select grp.OrderByDescending(g => g.Id).First();

var customers = from s in  Customers select s;

var balances = from s in lastEntries 
       join c in Customers on s.Customer.Id equals c.Id 
       where c.Balance !=  s.CFwd 
       select s;

 balances .Dump();

Update

The following is the SQL statement generated by NHibernate

select statement0_.Id as Id0_, 
statement0_.TransactionDate as Transact2_0_, 
statement0_.RefNo as RefNo0_, 
statement0_.Description as Descript4_0_, 
statement0_.BFwd as BFwd0_, 
statement0_.Amount as Amount0_, 
statement0_.CFwd as CFwd0_, 
statement0_.TransactionTypeId as Transact8_0_, 
statement0_.CustomerId as CustomerId0_ 
from Statement statement0_, Customer customer1_ 
where customer1_.Id=statement0_.CustomerId
group by statement0_.CustomerId 
having customer1_.Balance<>statement0_.AmountCFwd

while LINQPad generates the following

SELECT [t5].[test], [t5].[Id], [t5].[TransactionDate], 
[t5].[CustomerId], [t5].[RefNo], [t5].[Description], 
[t5].[BFwd], [t5].[Amount], [t5].[CFwd], 
[t5].[TransactionTypeId]
FROM (
    SELECT [t1].[Id]
    FROM [Statement] AS [t0]
    INNER JOIN [Customer] AS [t1] ON [t1].[Id] = [t0].[CustomerId]
    GROUP BY [t1].[Id]
    ) AS [t2]
OUTER APPLY (
    SELECT TOP (1) 1 AS [test], [t3].[Id], [t3].[TransactionDate], 
     [t3].[CustomerId], [t3].[DocRefNo], [t3].[Description], 
     [t3].[BFwd], [t3].[Amount], [t3].[CFwd], 
     [t3].[TransactionTypeId]
    FROM [Statement] AS [t3]
    INNER JOIN [Customer] AS [t4] ON [t4].[Id] = [t3].[CustomerId]
    WHERE [t2].[Id] = [t4].[Id]
    ORDER BY [t3].[Id] DESC
    ) AS [t5]
INNER JOIN [Customer] AS [t6] ON (
    SELECT [t7].[Id]
    FROM [Customer] AS [t7]
    WHERE [t7].[Id] = [t5].[CustomerId]
    ) = [t6].[Id]
WHERE [t6].[Balance] <> [t5].[CFwd]
ORDER BY [t5].[Id] DESC

I have tried rearranging the statements to allow grouping and don't seem to get it right.

Which is the correct syntax that will prevent the errors?

When you test in LINQPad, are you running it against the same database? — Nathan, Feb 03 '13 at 19:52
Yes. I copied the statements from LINQPad and replaced the pluralized table names with NHibernate replacements. — kagundajm, Feb 03 '13 at 20:22
Another thing that might be helpful is to post the underlying SQL query that is getting generated. http://stackoverflow.com/questions/129133/how-do-i-view-the-sql-that-is-generated-by-nhibernate — Nathan, Feb 03 '13 at 22:05

score 1 · Answer 1 · answered Feb 04 '13 at 03:18

It turns out I was thinking the SQL way instead of objects. The only statement I require is

var lastEntries = from s in session.Query<Statement>()
              group s by s.Customer.Id into grp
              select grp.OrderByDescending(g => g.Id).First();

and add a grouping for all columns of the Statement object as follows

var lastEntries = from s in session.Query<Statement>()
              group s by new 
                    {
                       s.Customer
                      , s.Id
                       ...
                    } into grp
              select grp.OrderByDescending(g => g.Id).First();

score 0 · Answer 2 · answered Feb 03 '13 at 21:14

0

I think it might be a matter of a simple typo.

In your example that works in LinqPad, your where clause compares against the joined Customers.

However, in your LinqToNHibernate example, your where clause compares against the s from lastEntries rather than the joined customer c.

So perhaps for your NHibernate example, you just need to do

var balances = from s in lastEntries 
            join c in customers on s.Customer.Id equals c.Id 
            where c.Balance !=  s.CFwd
            select s ;

answered Feb 03 '13 at 21:14

Nathan

10,593
10
63
87

Even re-arranging still produces the same exception. Seeing that NHibernate is complaining about grouping, I assume I am required to add a grouping clause on the last statement but I don't seen to get it right. – kagundajm Feb 04 '13 at 02:02
It wouldn't always necessarily have to be the last statement that is causing the issue, because LINQ uses deferred execution. – Nathan Feb 04 '13 at 03:04

LINQ NHibernate- Why do I get Column 'is invalid in the HAVING clause?

2 Answers2