Linqer cannot convert: SQL syntax error: Each GROUP BY expression must contain at least one column reference

Question

I have this running query:

SELECT DISTINCT
       b.NAME AS [Service Name],
       CASE
           WHEN [a].[PAY_MODE_ID] IN ( 1, 2 ) THEN
               'OFFLINE'
           ELSE
               'ONLINE'
       END AS [Transaction Type],
       p.NAME AS [Payment Method],
       SUM(a.REQUESTED_QTY) AS [Transaction],
       SUM(a.ITEM_TOTAL) AS Income
FROM dbo.BILL_INFO_DETAIL AS a
    INNER JOIN dbo.SERVICE_INFO AS b
        ON a.SERVICE_CODE = b.SERVICE_CODE
    INNER JOIN dbo.PAY_MODE AS p
        ON a.PAY_MODE_ID = p.PAY_MODE_ID
WHERE (a.INPUT_STATUS = '1')
      AND (b.SERVICE_CODE IN ( 1610, 1611, 1612 ))
      AND (CONVERT(VARCHAR(2), a.STAMP_DATE, 101) IN ( '10', '11', '12' ))
      AND (CONVERT(VARCHAR(4), a.STAMP_DATE, 102) IN ( '2017' ))
      AND (b.FEE > 1)
GROUP BY b.NAME,
         CASE
             WHEN [a].[PAY_MODE_ID] IN ( 1, 2 ) THEN
                 'OFFLINE'
             ELSE
                 'ONLINE'
         END,
         p.NAME
ORDER BY [Transaction Type];

Linqer is not able to convert this to proper LINQ:

SQL syntax error: Each GROUP BY expression must contain at least one column reference.

This query is working in SQL Server. Any pointers?

Note:

• Removing the DISTINCT has no effect
• Removing the ORDER BY has no effect
• Removing the ; has no effect

Answer 1

Seems that all I had to do was to remove the CASE with PAY_MODE_ID. I modified the query apart from that change in order to make it production ready:

SELECT si.SERVICE_CODE,     
       si.NAME AS [ServiceName],
       bid.PAY_MODE_ID AS [PaymentId],
       p.NAME AS [PaymentName],
       SUM(bid.REQUESTED_QTY) AS [Transaction],
       SUM(bid.ITEM_TOTAL) AS [Income]
FROM BILL_INFO_DETAIL AS bid
    INNER JOIN dbo.SERVICE_INFO AS si
        ON bid.SERVICE_CODE = si.SERVICE_CODE
    INNER JOIN dbo.PAY_MODE AS p
        ON bid.PAY_MODE_ID = p.PAY_MODE_ID
WHERE (bid.INPUT_STATUS = '1')
      AND (si.SERVICE_CODE IN ( 1610, 1611, 1612 ))
      AND (CONVERT(VARCHAR(2), bid.STAMP_DATE, 101) IN ( '10', '11', '12' ))
      AND (CONVERT(VARCHAR(4), bid.STAMP_DATE, 102) IN ( '2017' ))
      AND (si.FEE > 1)
GROUP BY si.SERVICE_CODE,
         si.NAME,   
         bid.PAY_MODE_ID,
         p.NAME

This resulted in LINQ (slightly modified):

    from bid in db.BILL_INFO_DETAIL
    where
      bid.INPUT_STATUS == true &&
      (new int[] {1610, 1611, 1612 }).Contains(bid.SERVICE_INFO.SERVICE_CODE) &&
      (new int[] {10, 11, 12 }).Contains(bid.STAMP_DATE.Value.Month) &&
      bid.STAMP_DATE.Value.Year == 2017 &&
      bid.SERVICE_INFO.FEE > 1
    group new {bid.SERVICE_INFO, bid, bid.PAY_MODE} by new {
      bid.SERVICE_INFO.SERVICE_CODE,
      bid.SERVICE_INFO.NAME,
      bid.PAY_MODE_ID,
      Column1 = bid.PAY_MODE.NAME
    } into g
    select new {
      g.Key.SERVICE_CODE,
      ServiceName = g.Key.NAME,
      PaymentId = g.Key.PAY_MODE_ID,
      PaymentName = g.Key.NAME,
      Transaction = (int?)g.Sum(p => p.bid.REQUESTED_QTY),
      Income = (decimal?)g.Sum(p => p.bid.ITEM_TOTAL)
    }

Answer 2

For translating SQL to LINQ query comprehension:

1. Translate FROM subselects as separately declared variables.
2. Translate each clause in LINQ clause order, translating monadic and aggregate operators (DISTINCT, TOP, MIN, MAX etc) into functions applied to the whole LINQ query.
3. Use table aliases as range variables. Use column aliases as anonymous type field names.
4. Use anonymous types (new { ... }) for multiple columns.
5. JOIN conditions that aren't all equality tests with AND must be handled using where clauses outside the join, or with cross product (from ... from ...) and then where
6. JOIN conditions that are multiple ANDed equality tests between the two tables should be translated into anonymous objects
7. LEFT JOIN is simulated by using into joinvariable and doing another from from the joinvariable followed by .DefaultIfEmpty().
8. Replace COALESCE with the conditional operator (?:)and a null test.
9. Translate IN to .Contains() and NOT IN to !...Contains(), using literal arrays or array variables for constant lists.
10. Translate x BETWEEN low AND high to low <= x && x <= high.
11. Translate CASE to the ternary conditional operator ?:.
12. SELECT * must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.
13. SELECT fields must be replaced with select new { ... } creating an anonymous object with all the desired fields or expressions.
14. Proper FULL OUTER JOIN must be handled with an extension method.

So for your query,

var ans = (from a in dbo.BILL_INFO_DETAIL
           join b in dbo.SERVICE_INFO on a.SERVICE_CODE equals b.SERVICE_CODE
           join p in dbo.PAY_MODE on a.PAY_MODE_ID equals p.PAY_MODE_ID
           where (a.INPUT_STATUS == "1") &&
                 (new[] { 1610, 1611, 1612 }.Contains(b.SERVICE_CODE)) &&
                 (new[] { 10, 11, 12 }.Contains(a.STAMP_DATE.Month)) &&
                 (new[] { 2017 }.Contains(a.STAMP_DATE.Year)) &&
                 (b.FEE > 1)
           let TransactionType = new[] { 1, 2 }.Contains(a.PAY_MODE_ID) ? "OFFLINE" : "ONLINE"
           group new { a, b, p } by new { bName = b.NAME, TransactionType, pName = p.NAME } into abpg
           orderby abpg.Key.TransactionType
           select new {
               Service_Name = abpg.Key.bName,
               TransactionType = abpg.Key.TransactionType,
               Payment_Method = abpg.Key.pName,
               Transaction = abpg.Sum(abp => abp.a.REQUESTED_QTY),
               Income = abpg.Sum(abp => abp.a.ITEM_TOTAL)
           }).Distinct();