title: LINQ NHibernate- Why do I get Column 'is invalid in the HAVING clause?
body: <p>I want to get the last entries in a table and join them with another table to produce entries which have a mismatch.</p>
  
  <p>I get the following error </p>
  
  <pre><code>Column is invalid in the HAVING clause 
  because it is not contained in either 
  an aggregate function or the GROUP BY clause
  </code></pre>
  
  <p>for both CFWD and Balance
  when running the following</p>
  
  <pre><code>var lastEntries = from s in session.Query&lt;Statement&gt;()
                    group s by s.Customer.Id into grp
                    select grp.OrderByDescending(g =&gt; g.Id).First();
  
   var customers = session.Query&lt;Customer&gt;();
  
   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 )
   {
       ...
   }
  </code></pre>
  
  <p>However, testing the same in <a href="http://www.linqpad.net/" rel="nofollow">LiNQPad</a> produces the expected results without errors</p>
  
  <pre><code>var lastEntries = from s in Statements
                    group s by s.Customer.Id into grp
                    select grp.OrderByDescending(g =&gt; 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();
  </code></pre>
  
  <p><strong>Update</strong></p>
  
  <p>The following is the SQL statement generated by NHibernate</p>
  
  <pre><code>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&lt;&gt;statement0_.AmountCFwd 
  </code></pre>
  
  <p>while LINQPad generates the following</p>
  
  <pre><code>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] &lt;&gt; [t5].[CFwd]
  ORDER BY [t5].[Id] DESC
  </code></pre>
  
  <p>I have tried rearranging the statements to allow grouping and don't seem to get it right.</p>
  
  <p>Which is the correct syntax  that will prevent the errors?</p>