I'm using MySQL and LLBLGEN version 3.5 Final (November 6th, 2012).

I have the 3 following tables : * sale_order * sale_order_detail * vat_rate

I want to create a query which return the amounts of the footer of an order which are for me : tax exclusive total amount, vat total amount and tax inclusive total amount.

In SQL, I can do it like this

select
  SUM((Quantity * Price) * (1 - IFNULL(sale_order.CashDiscount, 0))) AS TaxExclusiveTotalAmount,
  SUM((Quantity * Price) * (1 - IFNULL(sale_order.CashDiscount, 0)) * vat_rate.Rate) AS TaxTotalAmount,
  SUM((Quantity * Price) * (1 - IFNULL(sale_order.CashDiscount, 0)) * (1 + vat_rate.Rate)) AS TaxInclusiveTotalAmount
from sale_order_detail
inner join sale_order ON sale_order.Id = sale_order_detail.FK_sale_order_Id
inner join vat_rate ON vat_rate.Id = sale_order_detail.FK_vat_rate_Id
GROUP BY sale_order_detail.FK_sale_order_Id

In Linq, I write it like this :

 var footerAmountQuery1 =
                from saleOrderDetail in metaData.SaleOrderDetail
                join saleOrder in metaData.SaleOrder on saleOrderDetail.FkSaleOrderId equals saleOrder.Id
                join vatRate in metaData.VatRate on saleOrderDetail.FkVatRateId equals vatRate.Id into leftJoinedVatRates
                from leftJoinedVatRate in leftJoinedVatRates.DefaultIfEmpty()
                select new
                {
                    SaleOrderDetailId = saleOrderDetail.Id,
                    SaleOrderId = saleOrder.Id,
                    CashDiscount = (saleOrder.CashDiscount.HasValue) ? saleOrder.CashDiscount.Value : 0,
                    SaleOrderDetailTotalAmount = saleOrderDetail.Quantity * saleOrderDetail.Price,
                    VatRate = (leftJoinedVatRate.Rate == null) ? 0 : leftJoinedVatRate.Rate
                };

            var footerAmountQuery2 =
                from footerAmount in footerAmountQuery1
                group footerAmount by new { footerAmount.SaleOrderId, footerAmount.CashDiscount } into footerAmountGroup
                select new FooterAmount
                {
                    SaleOrderId = footerAmountGroup.Key.SaleOrderId,
                    OrderDetailsTotalAmount = footerAmountGroup.Sum(o =>  o.SaleOrderDetailTotalAmount),
                    TaxExclusiveTotalAmount = footerAmountGroup.Sum(o =>  o.SaleOrderDetailTotalAmount * (1 - footerAmountGroup.Key.CashDiscount)),
                    TaxTotalAmount        = footerAmountGroup.Sum(o => (o.SaleOrderDetailTotalAmount * (1 - footerAmountGroup.Key.CashDiscount)) * o.VatRate),
                    TaxInclusiveTotalAmount = footerAmountGroup.Sum(o => (o.SaleOrderDetailTotalAmount * (1 - footerAmountGroup.Key.CashDiscount)) * (1 + o.VatRate))
                };

            FooterAmount result = footerAmountQuery2.Single();

but I have the following error Unknown column 'LPA_L1.CashDiscount' in 'field list'

I'm also surprised that the previous Linq query is translated to SQL using a different SELECT for each SUM function (see below).

SELECT `LPA_L1`.`SaleOrderId`,
 `LPA_L1`.`LPAV_` AS `OrderDetailsTotalAmount`,
 `LPA_L1`.`LPAV_1` AS `TaxExclusiveTotalAmount`,
 `LPA_L1`.`LPAV_2` AS `TaxTotalAmount`,
 `LPA_L1`.`LPAV_3` AS `TaxInclusiveTotalAmount` FROM (SELECT `LPA_L3`.`SaleOrderId`,
 `LPA_L3`.`CashDiscount`,
 SUM(`LPA_L3`.`SaleOrderDetailTotalAmount`) AS `LPAV_`,
 SUM(`LPA_L3`.`LPAV_`) AS `LPAV_1`,
 SUM(`LPA_L3`.`LPAV_1`) AS `LPAV_2`,
 SUM(`LPA_L3`.`LPAV_2`) AS `LPAV_3`
 FROM
 (
   SELECT `LPA_L4`.`SaleOrderId`,
 `LPA_L4`.`CashDiscount`,
 `LPA_L4`.`LPAV_`,
 `LPA_L4`.`SaleOrderDetailTotalAmount`,
 `LPA_L4`.`VatRate`,
 `LPA_L4`.`LPAV_1`,
 ((`LPA_L4`.`SaleOrderDetailTotalAmount` * (1 - `LPA_L4`.`CashDiscount`)) * (1 + `LPA_L4`.`VatRate`)) AS `LPAV_2` 
 FROM 
 (
    SELECT `LPA_L5`.`SaleOrderId`,
    `LPA_L5`.`CashDiscount`,
    `LPA_L5`.`LPAV_`,
    `LPA_L5`.`SaleOrderDetailTotalAmount`,
    `LPA_L5`.`VatRate`,
    ((`LPA_L5`.`SaleOrderDetailTotalAmount` * (1 - `LPA_L5`.`CashDiscount`)) * `LPA_L5`.`VatRate`) AS `LPAV_1` 
    FROM 
    (
        SELECT `LPA_L6`.`SaleOrderId`,
        `LPA_L6`.`CashDiscount`,
        (`LPA_L6`.`SaleOrderDetailTotalAmount` * (1 - `LPA_L6`.`CashDiscount`)) AS `LPAV_`,
        `LPA_L6`.`SaleOrderDetailTotalAmount`,
        `LPA_L1`.`CashDiscount`,
        `LPA_L6`.`VatRate` 
        FROM 
        (
            SELECT `LPA_L7`.`Id` AS `SaleOrderDetailId`,
            `LPA_L8`.`Id` AS `SaleOrderId`,
            CASE WHEN CASE WHEN ( `LPA_L8`.`CashDiscount` IS NOT NULL) THEN 1 ELSE 0 END=1 THEN `LPA_L8`.`CashDiscount` ELSE 0 END AS `CashDiscount`,
            (`LPA_L7`.`Quantity` * `LPA_L7`.`Price`) AS `SaleOrderDetailTotalAmount`,
            CASE WHEN CASE WHEN ( `LPA_L9`.`Rate` IS NULL) THEN 1 ELSE 0 END=1 THEN 0 ELSE `LPA_L9`.`Rate` END AS `VatRate` 
            FROM (( `sale_order_detail` `LPA_L7`  
            INNER JOIN `sale_order` `LPA_L8`  ON  `LPA_L7`.`FK_sale_order_Id` = `LPA_L8`.`Id`) 
            LEFT JOIN `vat_rate` `LPA_L9`  ON  `LPA_L7`.`FK_vat_rate_Id` = `LPA_L9`.`Id`) 
            WHERE ( ( ( `LPA_L8`.`Id` = 1)))) `LPA_L6`) `LPA_L5`) `LPA_L4`) `LPA_L3` 
            GROUP BY `LPA_L3`.`SaleOrderId`, `LPA_L3`.`CashDiscount`
        ) `LPA_L1` LIMIT 1

I attach a simple test project.

please make sure you're using the latest runtime builds. Could you try those please?

Same error with the latest build (2013-04-29)

Ok, could you please trim the query down to the minimum query which reproduces the problem? This is because otherwise it's impossible for us to find what's wrong as it's not possible to reproduce it.

In fact, for me, there is 2 problems : 1) the error 2) the fact that a SELECT SUM(X), SUM(Y), SUM(Z) implies 3 SUB SELECT.

I attached a project (with database script) which reproduce the problem. In the footerAmountQuery2 of this project, if you comment the TaxTotalAmount and TaxInclusiveTotalAmount, it runs. If you uncomment one, the error occurs.

            var footerAmountQuery2 =
                from footerAmount in footerAmountQuery1
                group footerAmount by new { footerAmount.SaleOrderId, footerAmount.CashDiscount } into footerAmountGroup
                select new FooterAmount
                {
                    SaleOrderId = footerAmountGroup.Key.SaleOrderId,
                    OrderDetailsTotalAmount = footerAmountGroup.Sum(o => o.SaleOrderDetailTotalAmount),
                    TaxExclusiveTotalAmount = footerAmountGroup.Sum(o => o.SaleOrderDetailTotalAmount * (1 - footerAmountGroup.Key.CashDiscount)),
                    TaxTotalAmount       = footerAmountGroup.Sum(o => (o.SaleOrderDetailTotalAmount * (1 - footerAmountGroup.Key.CashDiscount)) * o.VatRate),
                    TaxInclusiveTotalAmount = footerAmountGroup.Sum(o => (o.SaleOrderDetailTotalAmount * (1 - footerAmountGroup.Key.CashDiscount)) * (1 + o.VatRate))
                };

There's nothing attached.

About the 3 nested queries: groupby in linq isn't equal to groupby in SQL. The groupby is placed at a different level than the aggregate methods and the aggregate methods work on a set of grouped elements with keys, while in SQL it's 1 set that's grouped and all aggregates work on that. To convert the tree of expressions in linq to SQL, we use the pattern you'll see in the SQL query, as it's the only way to convert the linq query to SQL (again, groupby in linq is different from group by in sql). If you want the sql query to be as you anticipated, please use queryspec instead: it gives you greater power over how the SQL will be as the query api is designed to make it easy to write predictable queries. (i.e. you'll know how the SQL will look like)

I attached it during the thread 's creation so the attachment is on the first post.

I did run your test. IMHO, you should remove the footerAmount.CashDiscount from the group, as you are using it in your calculations further in the code. Something like

var footerAmountQuery2 =
    from footerAmount in footerAmountQuery1
    group footerAmount by new { footerAmount.SaleOrderId } into footerAmountGroup
    select new FooterAmount
    {
        SaleOrderId = footerAmountGroup.Key.SaleOrderId,
        OrderDetailsTotalAmount = footerAmountGroup.Sum(o =>  o.SaleOrderDetailTotalAmount),
        TaxExclusiveTotalAmount = footerAmountGroup.Sum(o =>  o.SaleOrderDetailTotalAmount * (1 - o.CashDiscount)),
        TaxTotalAmount        = footerAmountGroup.Sum(o => (o.SaleOrderDetailTotalAmount * (1 - o.CashDiscount)) * o.VatRate),
        TaxInclusiveTotalAmount = footerAmountGroup.Sum(o => (o.SaleOrderDetailTotalAmount * (1 - o.CashDiscount)) * (1 + o.VatRate))
    };

You approximate SQL doesn't have it anyway. Removing that makes the code work. The way it generates the queries was explained by Frans.

Oh sorry...

I removed it and it's now OK.

Thanks for your fast answer.