LINQ: Group by and Any

Question

When I send this LINQ query to my database

var temp = dbContext.CustomerOrders
                        .Where(myOrder => myOrder.CustomerID == CustomerID
                                && myOrder.CustomerOrderLines.Any(ol => ol.Article != null && ol.Article.ArticleCode.Contains(TheValue)))                            
                        .OrderByDescending(myOrder => myOrder.ID)
                        .GroupBy(myOrder => myOrder.ID
                        , (g) => new CustomerControlOrderInfo()
                        {
                            CustomerOrderID = g.ID,
                            OrderDate = g.OrderDate,
                            NrOffArticles = string.Format("{0:N0}", g.CustomerOrderLines.Sum(ol => ol.Amount).ToString()),
                            AmountBeforeVAT = string.Format("{0:C}", g.CustomerOrderLines.Sum(o => o.TotalPriceExcl * o.Amount)),
                            //AmountAfterVAT = string.Format("{0:C2}", g.CustomerOrderLines.Sum(o => o.TotalPriceIncl * o.Amount).ToString()),
                            InvoiceNr = g.CustomerInvoices.Any() ? g.CustomerInvoices.LastOrDefault().InvoiceNumber : string.Empty,
                            Agent = g.Agent.Name,
                            Credit = g.CreditOrder,
                            Consignment = g.Consignment,
                            Approved = g.Approved,
                            Invoiced = g.CustomerOrderLines.Count(ol => ol.Invoiced == true) > 0 ? true : false,
                            Send = g.CustomerOrderLines.Count(ol => ol.SendDate != null) > 0 ? true : false
                        })
                        .ToList();

It results in an error "subquery returns more than row". This happens if the CustomerOrderline has more than on CustomerOrderLine.

When I look at the resulting SQL there seems a GROUP By missing. Edit:, SQL Added

SELECT a.[i_d] AS COL1, a.[i_d] AS COL2, a.[OrderDate] AS COL3
,  (    SELECT 1
    FROM [CustomerOrderLine] f 
    WHERE a.[i_d] = f.[CustomerOrderID])  AS  EXPR4
, c.[Name] AS COL5
,  (CASE WHEN a.[CreditOrder] <> 0 THEN 1 ELSE 0 END)  AS  EXPR6
,  (CASE WHEN a.[Consignment] <> 0 THEN 1 ELSE 0 END)  AS  EXPR7
,  (CASE WHEN a.[Approved] <> 0 THEN 1 ELSE 0 END)  AS  EXPR8
,  (    SELECT 1                  
    FROM [CustomerOrderLine] g 
    WHERE a.[i_d] = g.[CustomerOrderID])  AS  EXPR9
,  (    SELECT 1                  
    FROM [CustomerOrderLine] h 
    WHERE a.[i_d] = h.[CustomerOrderID])  AS  EXPR10 
FROM [CustomerOrder] a 
    LEFT JOIN [Agent] AS b ON (a.[AgentID] = b.[i_d]) 
    LEFT JOIN [Person] AS c ON (b.[i_d] = c.[i_d]) 
WHERE a.[i_d] = 7085                                       
AND a.[CustomerID] = 185 
AND EXISTS 
(   SELECT 1                  
    FROM [CustomerOrderLine] d 
    JOIN [Article] AS e ON (d.[ArticleID] = e.[i_d]) 
WHERE a.[i_d] = d.[CustomerOrderID] 
AND d.[ArticleID] IS NOT NULL 
AND e.[ArticleCode] LIKE '%mgpn%') 
ORDER BY COL1 DESC

What do I need to change to correct this?

I tried it with .All in the LINQ:

var temp = dbContext.CustomerOrders
                        .Where(myOrder => myOrder.CustomerID == CustomerID
                                && myOrder.CustomerOrderLines.All(ol => ol.Article != null && ol.Article.ArticleCode.Contains(TheValue)))                            
                        .OrderByDescending(myOrder => myOrder.ID)
                        .GroupBy(myOrder => myOrder.ID
                        , (g) => new CustomerControlOrderInfo()
                        {
                            CustomerOrderID = g.ID,
                            OrderDate = g.OrderDate,
                            NrOffArticles = string.Format("{0:N0}", g.CustomerOrderLines.Sum(ol => ol.Amount).ToString()),
                            AmountBeforeVAT = string.Format("{0:C}", g.CustomerOrderLines.Sum(o => o.TotalPriceExcl * o.Amount)),
                            //AmountAfterVAT = string.Format("{0:C2}", g.CustomerOrderLines.Sum(o => o.TotalPriceIncl * o.Amount).ToString()),
                            InvoiceNr = g.CustomerInvoices.Any() ? g.CustomerInvoices.LastOrDefault().InvoiceNumber : string.Empty,
                            Agent = g.Agent.Name,
                            Credit = g.CreditOrder,
                            Consignment = g.Consignment,
                            Approved = g.Approved,
                            Invoiced = g.CustomerOrderLines.Count(ol => ol.Invoiced == true) > 0 ? true : false,
                            Send = g.CustomerOrderLines.Count(ol => ol.SendDate != null) > 0 ? true : false
                        })
                        .ToList();

This results in correct SQL but not all orders are found (missing are orders with more than one line):

SELECT a.[i_d] AS COL1, a.[i_d] AS COL2, a.[OrderDate] AS COL3
,  (    SELECT 1                  
    FROM [CustomerOrderLine] f 
    WHERE a.[i_d] = f.[CustomerOrderID])  AS  EXPR4
, c.[Name] AS COL5
,  (CASE WHEN a.[CreditOrder] <> 0 THEN 1 ELSE 0 END)  AS  EXPR6
,  (CASE WHEN a.[Consignment] <> 0 THEN 1 ELSE 0 END)  AS  EXPR7
,  (CASE WHEN a.[Approved] <> 0 THEN 1 ELSE 0 END)  AS  EXPR8
,  (    SELECT 1                  
    FROM [CustomerOrderLine] g 
    WHERE a.[i_d] = g.[CustomerOrderID])  AS  EXPR9
,  (    SELECT 1                  
    FROM [CustomerOrderLine] h 
    WHERE a.[i_d] = h.[CustomerOrderID])  AS  EXPR10 
FROM [CustomerOrder] a 
    LEFT JOIN [Agent] AS b ON (a.[AgentID] = b.[i_d]) 
    LEFT JOIN [Person] AS c ON (b.[i_d] = c.[i_d]) 
WHERE a.[i_d] = 7085
AND a.[CustomerID] = 185
AND  
(   SELECT  (CASE WHEN COUNT(*) = 0 THEN 1 ELSE 0 END)                   
FROM [CustomerOrderLine] d 
WHERE a.[i_d] = d.[CustomerOrderID] 
AND not (
            EXISTS 
            (   SELECT 1234567 
                FROM [Article] e 
                WHERE d.[ArticleID] = e.[i_d] 
                AND d.[ArticleID] IS NOT NULL 
                AND e.[ArticleCode] LIKE '%mgpn%'
            )
        )
)  <> 0 ORDER BY COL1 DESC

Jeroen

Answer 1

jdweng put me the right track, adjusted the LINQ:

var temp = dbContext.CustomerOrders
                        .Where(myOrder => myOrder.CustomerID == CustomerID
                                && myOrder.CustomerOrderLines.Any(ol => ol.Article != null && ol.Article.ArticleCode.Contains(TheValue)))                            
                        .OrderByDescending(myOrder => myOrder.ID).ToList()
                        .GroupBy(myOrder => myOrder.ID
                        , (g) => new CustomerControlOrderInfo()
                        {
                            CustomerOrderID = g.ID,
                            OrderDate = g.OrderDate,
                            NrOffArticles = string.Format("{0:N0}", g.CustomerOrderLines.Sum(ol => ol.Amount).ToString()),
                            AmountBeforeVAT = string.Format("{0:C}", g.CustomerOrderLines.Sum(o => o.TotalPriceExcl * o.Amount)),
                            //AmountAfterVAT = string.Format("{0:C2}", g.CustomerOrderLines.Sum(o => o.TotalPriceIncl * o.Amount).ToString()),
                            InvoiceNr = g.CustomerInvoices.Any() ? g.CustomerInvoices.LastOrDefault().InvoiceNumber : string.Empty,
                            Agent = g.Agent.Name,
                            Credit = g.CreditOrder,
                            Consignment = g.Consignment,
                            Approved = g.Approved,
                            Invoiced = g.CustomerOrderLines.Count(ol => ol.Invoiced == true) > 0 ? true : false,
                            Send = g.CustomerOrderLines.Count(ol => ol.SendDate != null) > 0 ? true : false
                        })
                        .ToList();

This results in the following SQL:

SELECT a.[i_d] AS COL1
FROM [CustomerOrder] a 
WHERE a.[CustomerID] = 185
AND EXISTS 
(   SELECT 1                  
FROM [CustomerOrderLine] b 
    JOIN [Article] AS c ON (b.[ArticleID] = c.[i_d]) 
WHERE a.[i_d] = b.[CustomerOrderID] 
AND b.[ArticleID] IS NOT NULL 
AND c.[ArticleCode] LIKE '%mgpn%') 
ORDER BY COL1 DESC

This avoids the need for a GROUP BY, and results in the complete list.

Jeroen