Hi,

I'm stuck with the following query:

            var result4c = metaData.Order
                .Where(o => o.OrderId > 600215 && o.CompanyId == 343)
                .GroupBy(o => o.CreatedUTC.Value.Date)
                .Select(group => new { intervalStart = group.Key, revenue = group.Sum(o => o.OrderitemCollection.Sum(oi => oi.ProductPriceIn * oi.Quantity)) }).ToList();

This is intended to get the Order Revenue per Day based on the Orderitems of the Orders matching the Where clause. When I run this query I get an exception that column CompanyId is not found. This can be explained when looking at the query where the where clause is applied to the Orderitem part in the query (how should I state that?) instead of Order.

The where goes on LPLA_3 which is defined as: FROM [ObymobiDevelopment].[dbo].[Orderitem] [LPLA_3]

This is the generated query:

SELECT
  [LPA_L1].[LPFA_1] AS [intervalStart],
  [LPA_L1].[LPAV_] AS [revenue]
FROM (SELECT
  [LPA_L3].[LPFA_1],
  SUM([LPA_L3].[LPAV_]) AS [LPAV_]
FROM (SELECT
  DATEADD(ms, -DATEPART(ms, [LPLA_1].[CreatedUTC]), DATEADD(s, -DATEPART(s, [LPLA_1].[CreatedUTC]), DATEADD(n, -DATEPART(n, [LPLA_1].[CreatedUTC]), DATEADD(hh, -DATEPART(hh, [LPLA_1].[CreatedUTC]), [LPLA_1].[CreatedUTC])))) AS [LPFA_1],
  [LPLA_1].[OrderId],
  (SELECT
    SUM([LPA_L4].[LPAV_]) AS [LPAV_]
  FROM (SELECT
    [LPLA_3].[OrderId],
    ([LPLA_3].[ProductPriceIn] * CONVERT(decimal, [LPLA_3].[Quantity])) AS [LPAV_]
  FROM [ObymobiDevelopment].[dbo].[Orderitem] [LPLA_3]
  WHERE (([LPLA_1].[OrderId] = [LPLA_3].[OrderId]))) [LPA_L4])
  AS [LPAV_]
FROM [ObymobiDevelopment].[dbo].[Order] [LPLA_1]) [LPA_L3]
WHERE (((([LPA_L3].[OrderId] > 600125)
AND ([LPA_L3].[CompanyId] = 343))))
GROUP BY [LPA_L3].[LPFA_1]) [LPA_L1]

This is done with SelfServicing, LLBLGen 4.1.14.521

Do I make a mistake in writing my query? Is this simply not possible? Is this a bug?

Hope you can assist, thanks.

Please use a more recent build first.

We also don't fix bugs in 4.1 anymore, only in v4.2 and 5.0.

I've done as you proposed. I've updated all of our projects to 4.1.14.1023 (which didn't fix it) and then to 4.2.16.0905 (Runtime build 09052016). Unfortunately there's still the same issue, hope you can advice.

Additional info on the issue, the following does work:

            var result4 = metaData.Orderitem
                .Where(predicate)
                .GroupBy(oi => oi.OrderEntity.CreatedUTC.Value.Date)
                .Select(group => new { intervalStart = group.Key, revenue = group.Sum(oi => (oi.ProductPriceIn)) }).ToList();

Which leads to this query:

SELECT
  [LPA_L1].[LPFA_1] AS [intervalStart],
  [LPA_L1].[LPAV_] AS [revenue]
FROM (SELECT
  DATEADD(ms, -DATEPART(ms, [LPA_L3].[CreatedUTC]), DATEADD(s, -DATEPART(s, [LPA_L3].[CreatedUTC]), DATEADD(n, -DATEPART(n, [LPA_L3].[CreatedUTC]), DATEADD(hh, -DATEPART(hh, [LPA_L3].[CreatedUTC]), [LPA_L3].[CreatedUTC])))) AS [LPFA_1],
  SUM([LPA_L4].[ProductPriceIn]) AS [LPAV_]
FROM ([ObymobiDevelopment].[dbo].[Order] [LPA_L3]
INNER JOIN [ObymobiDevelopment].[dbo].[Orderitem] [LPA_L4]
  ON [LPA_L3].[OrderId] = [LPA_L4].[OrderId])
WHERE (((([LPA_L3].[OrderId] > @p1)
AND ([LPA_L3].[CompanyId] = @p2))))
GROUP BY DATEADD(ms, -DATEPART(ms, [LPA_L3].[CreatedUTC]), DATEADD(s, -DATEPART(s, [LPA_L3].[CreatedUTC]), DATEADD(n, -DATEPART(n, [LPA_L3].[CreatedUTC]), DATEADD(hh, -DATEPART(hh, [LPA_L3].[CreatedUTC]), [LPA_L3].[CreatedUTC]))))) [LPA_L1]

As soon as I included 'Quantity' it throws the error:

An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_2.OrderId" could not be bound. The multi-part identifier "LPLA_2.CompanyId" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Starting from the Orderitem:

                var result4b = metaData.Orderitem
                    .Where(oi => oi.OrderEntity.OrderId > 600215 && oi.OrderEntity.CompanyId == 343)
                    .GroupBy(oi => oi.OrderEntity.CreatedUTC.Value.Date)
                    .Select(group => new { intervalStart = group.Key, revenue = group.Sum(oi => (oi.ProductPriceIn * oi.Quantity)) }).ToList();

Leads to:

SELECT
  [LPA_L1].[LPFA_1] AS [intervalStart],
  [LPA_L1].[LPAV_] AS [revenue]
FROM (SELECT
  [LPA_L3].[LPFA_1],
  SUM([LPA_L3].[LPAV_]) AS [LPAV_]
FROM (SELECT
  DATEADD(ms, -DATEPART(ms, [LPA_L4].[CreatedUTC]), DATEADD(s, -DATEPART(s, [LPA_L4].[CreatedUTC]), DATEADD(n, -DATEPART(n, [LPA_L4].[CreatedUTC]), DATEADD(hh, -DATEPART(hh, [LPA_L4].[CreatedUTC]), [LPA_L4].[CreatedUTC])))) AS [LPFA_1],
  ([LPA_L5].[ProductPriceIn] * CONVERT(decimal, [LPA_L5].[Quantity])) AS [LPAV_]
FROM ([ObymobiDevelopment].[dbo].[Order] [LPA_L4]
INNER JOIN [ObymobiDevelopment].[dbo].[Orderitem] [LPA_L5]
  ON [LPA_L4].[OrderId] = [LPA_L5].[OrderId])) [LPA_L3]
WHERE (((([LPLA_2].[OrderId] > @p1)
AND ([LPLA_2].[CompanyId] = @p2))))
GROUP BY [LPA_L3].[LPFA_1]) [LPA_L1]

Starting from the Order also give an error:

An exception was caught during the execution of a retrieval query: Invalid column name 'CompanyId'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

                var result4c = metaData.Order
                    .Where(o => o.OrderId > 600215 && o.CompanyId == 343)
                    .GroupBy(o => o.CreatedUTC.Value.Date)
                    .Select(group => new { intervalStart = group.Key, revenue = group.Sum(o => o.OrderitemCollection.Sum(oi => (oi.ProductPriceIn * oi.Quantity))) }).ToList();

Leads to:

SELECT
  [LPA_L1].[LPFA_1] AS [intervalStart],
  [LPA_L1].[LPAV_] AS [revenue]
FROM (SELECT
  [LPA_L3].[LPFA_1],
  SUM([LPA_L3].[LPAV_]) AS [LPAV_]
FROM (SELECT
  DATEADD(ms, -DATEPART(ms, [LPLA_1].[CreatedUTC]), DATEADD(s, -DATEPART(s, [LPLA_1].[CreatedUTC]), DATEADD(n, -DATEPART(n, [LPLA_1].[CreatedUTC]), DATEADD(hh, -DATEPART(hh, [LPLA_1].[CreatedUTC]), [LPLA_1].[CreatedUTC])))) AS [LPFA_1],
  [LPLA_1].[OrderId],
  (SELECT
    SUM([LPA_L4].[LPAV_]) AS [LPAV_]
  FROM (SELECT
    [LPA_L5].[OrderId],
    ([LPA_L5].[ProductPriceIn] * CONVERT(decimal, [LPA_L5].[Quantity])) AS [LPAV_]
  FROM (SELECT
    [LPLA_8].[OrderitemId],
    [LPLA_8].[OrderId],
    [LPLA_8].[ProductId],
    [LPLA_8].[ProductDescription] AS [XProductDescription],
    [LPLA_8].[ProductName],
    [LPLA_8].[ProductPriceIn],
    [LPLA_8].[Quantity],
    [LPLA_8].[PriceIn] AS [XPriceIn],
    [LPLA_8].[VatPercentage],
    [LPLA_8].[Notes],
    [LPLA_8].[BenchmarkInformation],
    [LPLA_8].[BenchmarkProcessed],
    [LPLA_8].[Created],
    [LPLA_8].[CreatedBy],
    [LPLA_8].[Updated],
    [LPLA_8].[UpdatedBy],
    [LPLA_8].[Guid],
    [LPLA_8].[CategoryId],
    [LPLA_8].[Color],
    [LPLA_8].[CategoryName],
    [LPLA_8].[OrderSource],
    [LPLA_8].[ParentCompanyId],
    [LPLA_8].[CreatedUTC],
    [LPLA_8].[UpdatedUTC],
    [LPLA_8].[BenchmarkProcessedUTC]
  FROM [ObymobiDevelopment].[dbo].[Orderitem] [LPLA_8]
  WHERE (([LPLA_1].[OrderId] = [LPLA_8].[OrderId]))) [LPA_L5]) [LPA_L4])
  AS [LPAV_]
FROM [ObymobiDevelopment].[dbo].[Order] [LPLA_1]) [LPA_L3]
WHERE (((([LPA_L3].[OrderId] > @p1)
AND ([LPA_L3].[CompanyId] = @p2))))
GROUP BY [LPA_L3].[LPFA_1]) [LPA_L1]

I hope this can help so see if this is a bug or that I'm making a mistake in my code. For now upgrading to 5.0 is not an option for this code base, we stick to 4.* for now.

For now upgrading to 5.0 is not an option for this code base, we stick to 4.* for now.

Just for the sake of testing, could you please try v.5.0.6.

Regardless, of whether it works in v5 or not (it might, linq provider got some fixes), I have no idea how to write it in SQL what you want, so let's start there.

Cramming navigations in the group by aggregates is a problematic issue, because the source of the grouped set is often not determinable from the expression tree, so we make guesses while interpreting it (as linq merges sets into new anonymous types, which offer the sets as properties, but doing that a couple of times in chains results in a problematic situation sometimes.

As the navigations are joins anyway, it's often better to write out the joins and then group that set, but as said, it's better to first get the proper sql query so it's clear what should be done. It then also makes it easy to write it as queryspec as a workaround.

What we have is: We have Orders which contain 0 to N OrderItems. Each Orderitem can contain 0 to N OrderitemAlterationitems (i.e. customisations & add-ons to the Orderitem). Those can also impact the revenue as they may give additional costs. Chain: Order - Orderitems (oi) - OrderitemAlterationitems (oiai)

So the revenue is SUM(oi.Quantity * (oi.ProductPriceIn + SUM(oiai.AlterationoptionPriceIn)))

So what we want is a bit more complex than the test query I put at the top of this thread. I've written out a query for what I would like to achieve. I understand I could do stuff in code, but would want the database to do all the work as it can be quite a bit of data.

(Btw, this might not be the best SQL query, but you're at fault there, LLBLGen as stopped me from writing production SQL queries for over 10 years )

SELECT
    OrderDateTime, 
    SUM((ProductPriceIn + ISNULL(AlterationsPriceIn, 0)) * Quantity) AS TotalRevenue
FROM 
(
    SELECT
        Quantity,
        ProductPriceIn,
        (
            SELECT SUM(OrderitemAlterationitem.AlterationoptionPriceIn)
            FROM OrderitemAlterationitem
            WHERE OrderitemAlterationitem.OrderitemId = Orderitem.OrderitemId
        ) AS AlterationsPriceIn,
        CAST([Order].CreatedUTC AS DATE) as OrderDateTime
    FROM Orderitem
    LEFT JOIN [Order]
        ON Orderitem.OrderId = [Order].OrderId
    LEFT JOIN [Deliverypoint]
        ON [Order].DeliverypointId = [Deliverypoint].DeliverypointId
    WHERE 
        [Order].CreatedUTC >= '2016-03-01 00:00:00'
        AND [Order].CreatedUTC <= '2016-03-06 23:59:59'
        AND [Order].CompanyId IN (343)
) orderItems
GROUP BY OrderDateTime
ORDER BY OrderDateTime

This is grouped by day, we also need per week, month, quarter and year.

Ok, there are a couple of issues. One is that grouping on a property which is actually a function call is not going to work in all cases in Linq, as the function call can't be used to find back the child tree. This fails:

var q = metaData.Order.Where(o=>o.CustomerId == "CHOPS")
                .GroupBy(o=>o.OrderDate.Value.Date);

So grouping on what you do will not always work due to the function call. This is an expression tree handling issue and I have no idea how to solve that (nor does anyone else for that matter except perhaps some unknown MS employee).

Anyway, the query you gave in the first post however doesn't fail due to that, but just a heads up for when you run into this as you use this aspect in your query.

I wrote a repro query which mimics your query and which fails too, with the same exception:

var q = metaData.Order.Where(o=>o.CustomerId == "CHOPS")
                .GroupBy(o=>o.OrderDate.Value.Date)
                .Select(g=>new {K = g.Key, r = g.Sum(x=>x.OrderDetails.Sum(od=>od.UnitPrice * od.Quantity))});

This fails in v5.0.6. This generates:

SELECT [LPA_L1].[LPFA_1] AS [K],
       [LPA_L1].[LPAV_]  AS [r]
FROM   (SELECT [LPA_L3].[LPFA_1],
               SUM([LPA_L3].[LPAV_]) AS [LPAV_]
        FROM   (SELECT DATEADD(ms,
                               -DATEPART(ms,
                                         [LPLA_1].[OrderDate]),
                               DATEADD(s,
                                       -DATEPART(s,
                                                 [LPLA_1].[OrderDate]),
                                       DATEADD(n,
                                               -DATEPART(n,
                                                         [LPLA_1].[OrderDate]),
                                               DATEADD(hh,
                                                       -DATEPART(hh,
                                                                 [LPLA_1].[OrderDate]),
                                                       [LPLA_1].[OrderDate]))))                              AS [LPFA_1],
                       [LPLA_1].[OrderID]                                                                      AS [OrderId],
                       (SELECT SUM([LPA_L4].[LPAV_]) AS [LPAV_]
                        FROM   (SELECT [LPA_L5].[OrderId],
                                       ([LPA_L5].[UnitPrice] * CONVERT(DECIMAL, [LPA_L5].[Quantity])) AS [LPAV_]
                                FROM   (SELECT [LPLA_8].[Discount],
                                               [LPLA_8].[OrderID]   AS [OrderId],
                                               [LPLA_8].[ProductID] AS [ProductId],
                                               [LPLA_8].[Quantity],
                                               [LPLA_8].[UnitPrice]
                                        FROM   [Northwind].[dbo].[Order Details] [LPLA_8]
                                        WHERE  (([LPLA_1].[OrderID] = [LPLA_8].[OrderID]))) [LPA_L5]) [LPA_L4]) AS [LPAV_]
                FROM   [Northwind].[dbo].[Orders] [LPLA_1]) [LPA_L3]
        WHERE  ((([LPA_L3].[CustomerID] = @p1)))
        GROUP  BY [LPA_L3].[LPFA_1]) [LPA_L1] 

It blows up rapidly because SQL can't do aggregates in the same scope. So the nested aggregates have to be pulled apart and folded into a nested query. The problem is actually the where clause which is in the same scope as the group by but due to the transformations moves to the outer part (as it has to be in the same scope as the group by statement). A couple of nested queries are created because of the navigation in the aggregates and the nested aggregates themselves. This is needed because Linq groupby is actually a groupjoin, not a groupby like SQL has.

This is a problem because it has to work on elements which aren't there. It's then an easy conclusion to move the where clause with the rest, but that's not going to work in all cases. As it stays in the outer query, the elements it works on have to be added to the inner query which are created. But that's not easy, as it then comes down to interpreting what the where lambda does. It's not as "simple" (it's not that simple, hence the quotes) as pulling the fields used in a projection and adding these to a nested query.

So this makes this a problem for which I have no solution. Changing the query to introduce a select + where before the group by wasn't successful, it only showed more details about how misleading Linq's group by is. I leave a link to a tweet here from Alex James, who was on the OData team and EDM team for EF and their linq provider: https://twitter.com/adjames/status/770979699556167680

So! enough linq drama: there's of course always a workaround. For situations like this, we wrote QuerySpec, to make sure users can always write a query, e.g. in the edge cases where Linq falls flat on its ugly face.

Instead it's better to write the query in queryspec which always works.

I'll use my repro query. The minimized SQL I could come up with which represents the same query is:

SELECT OOD.[OrderDate],
        SUM(OOD.Total) AS TotalPerOrderDate
FROM   (SELECT O.[OrderDate],
                (
                    SELECT SUM(OD.[UnitPrice] * OD.[Quantity])
                    FROM   [Northwind].[dbo].[Order Details] OD
                    WHERE  O.[OrderID] = OD.[OrderID]
                ) AS Total
        FROM   [Northwind].[dbo].[Orders] O
        WHERE  CustomerID = 'CHOPS'
        ) OOD
GROUP  BY OOD.[OrderDate]

Writing that in queryspec is fairly straight forward: (the only hairy bits are the aliasing of Order as the orderdetails are correlated to that so have to refer to it in the outer scope. On sql server I think you can get away with not aliasing it which saves you a lot of .Source() calls)

var qf = new QueryFactory();
var q = qf.Create()
            .Select(()=>new
            {
                OrderDate = qf.Field("OOD", "OrderDate").ToValue<DateTime>(),
                TotalPerOrderDate = qf.Field("OOD", "Total").Sum().ToValue<Decimal>()
            })
            .From(qf.Order.TargetAs("O")
                    .Where(OrderFields.CustomerId.Source("O").Equal("CHOPS"))
                    .Select(OrderFields.OrderDate.Source("O").As("OrderDate"),
                            qf.OrderDetail
                            .Where(OrderFields.OrderId.Source("O").Equal(OrderDetailFields.OrderId))
                            .Select((OrderDetailFields.UnitPrice * OrderDetailFields.Quantity).Sum())
                            .ToScalar()
                            .As("Total"))
                    .As("OOD"))
            .GroupBy(qf.Field<DateTime>("OOD", "OrderDate"));

Which gives the same SQL query as the one which works. this groups on datetime.

To group on 'Date', add a call to DateTimeFunctions.Date, like so:

.Select(DateTimeFunctions.Date(OrderFields.OrderDate.Source("O")).As("OrderDate"),

instead of:

.Select(OrderFields.OrderDate.Source("O").As("OrderDate"),

Other date functions are available. The group by groups on that field so you only have to specify it once. Hopefully this gives you enough information to work around this problem in the linq provider.

Thanks - I will work on this again Wednesday latest. (Just so you know I appreciate the answer and will look in to it seriously)

gabrielk wrote:

Thanks - I will work on this again Wednesday latest. (Just so you know I appreciate the answer and will look in to it seriously)

Let me know if you run into problems with the queryspec query.