I have 3 tables : order : corresponds to customer orders order_detail : corresponds to line of customer orders order_detail_commission : corresponds to commissions earned by commercial agent third_party_agent : corresponds to commercial agents

I would like to retrieve the total amount of commissions earned by commercial agents by order.

In SQL, I would write it like this :

select SUM(order_detail_commission.CommissionRate * order_detail.Price), third_party_agent.Name
from order_detail
join order_detail_commission on order_detail_commission.FK_order_detail_Id = order_detail.Id
join third_party_agent on third_party_agent.Id = order_detail_commission.FK_third_party_agent_Id
GROUP BY third_party_agent.Id

In Linq, I tried to write something like this :

var query =
from orderDetail in metaData.OrderDetail
join orderDetailCommission in metaData.OrderDetailCommission on orderDetail.Id equals orderDetailCommission.FkOrderDetailId
join thirdPartyAgent in metaData.ThirdPartyAgent on orderDetailCommission.FkThirdPartyAgentId equals thirdPartyAgent.Id
group orderDetailCommission by orderDetailCommission.FkThirdPartyAgentId into g
select new DeliveryAgentCommissionSummaryProjection
{
    Id = g.Key,
    CommissionAmount = g.Sum(i => i.OrderDetail.Quantity * i.CommissionRate * i.OrderDetail.Price),
    ThirdPartyAgentText = "Test"
};

which generate the following sql query :

SELECT `LPA_L1`.`FkThirdPartyAgentId` AS `Id`, `LPA_L1`.`LPAV_` AS `CommissionAmount`, 'Test' AS `LPFA_10`
FROM
(
  SELECT `LPA_L3`.`FkThirdPartyAgentId`, SUM(`LPA_L3`.`LPAV_`) AS `LPAV_`
  FROM
  (
    SELECT `LPA_L5`.`FK_third_party_agent_Id` AS `FkThirdPartyAgentId`, (`LPLA_7`.`Quantity` * `LPLA_5`.`CommissionRate`) * `LPLA_7`.`Price` AS `LPAV_`
    FROM
    (
      (
        (
          `order_detail` `LPA_L4` INNER JOIN `order_detail_commission` `LPA_L5`  ON  `LPA_L4`.`Id` = `LPA_L5`.`FK_order_detail_Id`
        )
        INNER JOIN `third_party_agent` `LPA_L7`  ON  `LPA_L5`.`FK_third_party_agent_Id` = `LPA_L7`.`Id`
      )
      INNER JOIN `third_party` `LPA_L6`  ON  `LPA_L6`.`Id`=`LPA_L7`.`Id`)
  ) `LPA_L3`
  GROUP BY `LPA_L3`.`FkThirdPartyAgentId`
) `LPA_L1

`

But the following error is thrown :

An exception was caught during the execution of a retrieval query: Unknown column 'LPLA_7.Quantity' in 'field list'. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

It's normal because the Quantity column doesn't exist in third_party_agent but in order_detail.

So I tried to split the Linq query in 2. In the first query, I create an anonymous type containing all the columns I need then I apply the group by clause on this anonymous type like this :

            var tempoQuery =
                from orderDetail in metaData.OrderDetail
                join order in metaData.Order on orderDetail.FkOrderId equals order.Id
                join orderDetailCommission in metaData.OrderDetailCommission on orderDetail.Id equals orderDetailCommission.FkOrderDetailId
                join thirdPartyAgent in metaData.ThirdPartyAgent on orderDetailCommission.FkThirdPartyAgentId equals thirdPartyAgent.Id
                select new
                {
                    FkThirdPartyAgentId = thirdPartyAgent.FkThirdPartyAgentId,
                    ThirdPartyAgentText = thirdPartyAgent.Name,
                    Quantity = orderDetail.Quantity,
                    CommissionRate = orderDetailCommission.CommissionRate,
                    Price = orderDetail.Price
                };

            var query =
                from deliveryAgentCommission in tempoQuery
                group deliveryAgentCommission by deliveryAgentCommission.FkThirdPartyAgentId into g
                select new DeliveryAgentCommissionSummaryProjection
                {
                    Id = g.Key,
                    ThirdPartyAgentId = g.Key,
                    ThirdPartyAgentText = ??????,
                    CommissionAmount = g.Sum(i => i.Quantity * i.CommissionRate * i.Price)
                };

But I don't know how to retrieve the name of the agent (third_party_agent.Name).

Please help me

Like SQL, any field in the select list should have an aggrgate on it, if i's not included in the GroupBy.

Try this:

var query =
                from deliveryAgentCommission in tempoQuery
                group deliveryAgentCommission by                                                 deliveryAgentCommission.FkThirdPartyAgentId into g
                select new DeliveryAgentCommissionSummaryProjection
                {
                    Id = g.Key,
                    ThirdPartyAgentId = g.Key,
                    ThirdPartyAgentText = g.Max(i=> i.ThirdPartyAgentText),
                    CommissionAmount = g.Sum(i => i.Quantity * i.CommissionRate * i.Price)
                };

I thought about it but when I run the application, the following exception is thrown "Unable to cast an object of type 'System.Double' to type 'System.String' ". ThirdPartyAgentText is a string property and Max doesn't seem to manage string value but only int, float, double, ...

In fact, I think I've found the solution : add field in the group by clause and access it usign the Key keyword.

            var query =
                from deliveryAgentCommission in tempoQuery
                group deliveryAgentCommission by new { deliveryAgentCommission.FkThirdPartyAgentId, deliveryAgentCommission.ThirdPartyAgentText } into g
                select new DeliveryAgentCommissionSummaryProjection
                {
                    Id = g.Key.FkThirdPartyAgentId,
                    ThirdPartyAgentId = g.Key.FkThirdPartyAgentId,
                    ThirdPartyAgentText = g.Key.ThirdPartyAgentText,
                    CommissionAmount = g.Sum(i => i.Quantity * i.CommissionRate * i.Price)
                };

1) Is it the real way to do it? 2) As I mentionned in my first post, I had to create a first query to put all the needed fields on the same projection and then create a second one to apply the group clause. Is it the way to do it or can I change this query to be able to access the OrderDetail field?

var query =
from orderDetail in metaData.OrderDetail
join orderDetailCommission in metaData.OrderDetailCommission on orderDetail.Id equals orderDetailCommission.FkOrderDetailId
join thirdPartyAgent in metaData.ThirdPartyAgent on orderDetailCommission.FkThirdPartyAgentId equals thirdPartyAgent.Id
group deliveryAgentCommission by new { deliveryAgentCommission.FkThirdPartyAgentId, deliveryAgentCommission.ThirdPartyAgentText } into g
select new DeliveryAgentCommissionSummaryProjection
{
    Id = g.Key.ThirdPartyAgentId,
    CommissionAmount = g.Sum(i => i.OrderDetail.Quantity * i.CommissionRate * i.OrderDetail.Price),
    ThirdPartyAgentText = g.Key.ThirdPartyAgentText
};

I don't see a problem in both approaches, both should give the same result if I'm not mistaken.

If you look at the first message of this thread, you will see that the i.OrderDetail.Quantity syntax throws an exception.

When using "[b]group by[/b]" clause, is it possible to access properties ot linked table (here order_detail linked to order_detail_commission)?

How would you write in Linq the following query?

select SUM(order_detail_commission.CommissionRate * order_detail.Price), third_party_agent.Name
from order_detail
join order_detail_commission on order_detail_commission.FK_order_detail_Id = order_detail.Id
join third_party_agent on third_party_agent.Id = order_detail_commission.FK_third_party_agent_Id
GROUP BY third_party_agent.Id

This is not the same query as yours but it's similar and it raises the same exception. Let say I want to obtain the total amount of money made by specific product. I will make the query larger than it could be just to show the problem:

var metaData = new LinqMetaData(adapter);
var q = from o in metaData.Order
        join od in metaData.OrderDetail on o.OrderId equals od.OrderId
        join p in metaData.Product on od.ProductId equals p.ProductId
        group od by od.Product.ProductName into g
        select new
        {
            Id = g.Key,
            Ammount = g.Sum(x => x.Quantity * x.UnitPrice)
        };

... that raises your exception, exactly on the same field. The problem is that the fields inside the expression are unknown by the groupBy subquery in the sql query.

As you asked, you could write a first query which represents the data you want to collapse in the group, then use a second one to do the actual groupBy:

var metaData = new LinqMetaData(adapter);
var q1 = from o in metaData.Order
            join od in metaData.OrderDetail on o.OrderId equals od.OrderId
            join p in metaData.Product on od.ProductId equals p.ProductId
            select new
            {
                ProductName = p.ProductName,
                Amount = (od.Quantity * od.UnitPrice)
            };


var q2 = from o in q1
            group o by o.ProductName into g
            select new
            {
                ProductName = g.Key,
                Amount = g.Sum(x => x.Amount)
            };

This was the solution I found (as mentionned in my first message). So there is no other solution?

Seems that's the only way.

Perhaps It would be great to think about developp this functionality (access to other entities in an aggregate function)

It's unfortunately a result of how Linq differs from SQL. With SQL, the aggregated fields are specified within the groupby scope (the groupby clause is in the same scope as the projection) and with linq this is not the case. Furthermore, groupby in linq is able to group in 1 scope over multiple aggregate fields, but in SQL this isn't the case, you have to nest each aggregated field inside its own query.

This requires what we call 'folding', fold each query inside another so in the end the right query is produced in SQL form. This is a transformation of how groupby in linq is defined to how groupby in sql is defined. To do this transformation we need information which isn't known in the query you wrote at the top which produces the exception (we try, but as you can see, the query produced isn't working).

I wished the designers of linq had had more eye for how sql works but instead defined groupby in linq as a set of sets (one per group).