COUNT(*) on Related Elements

Posts   
 
    
Kodiak
User
Posts: 92
Joined: 13-Apr-2009
# Posted on: 04-Sep-2015 00:48:17   

Hi,

I'm trying to do a query in LLBLGen 4.1 however it is not working as expected.

I am trying to find the Outstanding / Not Fully Paid invoices with the following:

invoices = invoices.Where(x => (x.AmountExTax + x.Tax) > (x.Payments.Count ? x.Payments.Where(y => !y.Archived).Sum(y => y.Amount) :0));

This is almost working however giving very strange results.

Checking the generated SQL, LLBLGen does not translate Count into Count(*) it actually grabs the first column in the database COUNT(Column1).

In our particular case this was null most of the time giving random results.

Is there a way to force LLBLGen to use COUNT(*) as this is what the documentation seems to indicate it should do.

Using the Any() method gives better SQL although still random columns

WHEN  EXISTS (SELECT [LPLA_2].[LastColumn] FROM 
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 04-Sep-2015 07:52:43   
invoices = invoices.Where(x => (x.AmountExTax + x.Tax) > x.Payments.Where(y => !y.Archived).Sum(y => y.Amount)));
David Elizondo | LLBLGen Support Team