CODEWITHSUNDEEP
c#linq
eric.bryan
eric.bryan

Reputation: 61

Linq syntax with group by and having clauses

I try to get a list from a datatable by a linq syntax with having clause.

But I don't get what I want.

Here's the SQL syntax :

SELECT ID_BEN,
        GAR1,
        FIRST(FIRST_NAME) FIRST_NAME,
        FIRST(LAST_NAME) LAST_NAME,
        FIRST(ADR1) ADR1,
        FIRST(ADR2) ADR2,
        FIRST(ZIP_CODE) ZIP_CODE,
        FIRST(CITY) CITY,
        SUM(AMOUNT) SUM_AMOUNT,
    SUM(CASE WHEN STATUS_TAB <> 'OK' THEN 1 ELSE 0 END) NUM_STATUS_TAB
    FROM T_AMOUNT
    WHERE STATUS_ENR = 'OK' AND STATE_ENR = '1'
    GROUP BY ID_BEN, GAR1
    HAVING SUM(CASE WHEN STATUS_TAB <> 'OK' THEN 1 ELSE 0 END) = 0

Here is my linq syntax :

var oLstReglementGrp = objDataSet.T_AMOUNT
                      .AsEnumerable()
                      .Select(sel => new
                       {
                           ID_BEN = sel.ID_BEN,
                           GAR1 = sel.GAR1,
                           LI_NOM = sel.First().FIRST_NAME,
                           LI_ADR1 = sel.First().LAST_NAME,
                           LI_ADR2 = sel.First().ADR1,
                           LI_ADR3 = sel.First().ADR2,
                           LI_ADR4 = sel.First().ZIP_CODE,
                           CD_PST = sel.First().CITY
                           
                       })
                     .Where(x => x.STATUS_ENR == "OK"
                          && x.STATE_ENR == "1")
                      .GroupBy(row => new { ID_BEN = (long?)row.ID_BEN, GAR1 = row.GAR1 },
                      (g, r) => new
                      {
                          NUM_STATUS_TAB = r.Sum(s => s.STATUS_TAB != "OK" ? 1 : 0),
                          SUM_AMOUNT = r.Sum(s => (decimal?)s.AMOUNT)
                      })
                      .Where(p => p.NUM_STATUS_TAB == 0)
                      .ToList();

Here are the SQL results :

FIRST_NAME  LAST_NAME   ADR1        ZIP_CODE    CITY        SUM_AMOUNT  NUM_STATUS_TAB
Jack        Palance     3 bd One    1234        New York    12000       0   
John        Wayne       4 ave 2     4567        Los Angeles 5500        0           
Jimmy       Page        5 street 2  2345        Chicago     450         0

And in my list from the linq :

SUM_AMOUNT  NUM_STATUS_TAB
12000       0   
5500        0           
450         0

Do you have an idea ?

Upvotes: 0

Views: 80

Answers (1)

xdtTransform
xdtTransform

Reputation: 2057

When writing LinQ you should no try to translate to SQL query like you would read it.
LinQ syntax is closer to SQL Execution order. In this way Linq is more "Logical".

SQL execution order is the following:

  1. FROM and JOINs
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
objDataSet
    // 1. FROM and JOINs
    .T_AMOUNT   
    
    //2. WHERE  
    .Where(x => x.STATUS_ENR == "OK" && x.STATE_ENR == "1") 
    
    //3. GROUP BY 
    .GroupBy(row => new { ID_BEN = (long?)row.ID_BEN, GAR1 = row.GAR1 })
    
    //5. SELECT 
    .Select(sel => new
    {
        ID_BEN = sel.Key.ID_BEN,
        GAR1 = sel.Key.GAR1,
        LI_NOM = sel.First().FIRST_NAME,
        LI_ADR1 = sel.First().LAST_NAME,
        LI_ADR2 = sel.First().ADR1,
        LI_ADR3 = sel.First().ADR2,
        LI_ADR4 = sel.First().ZIP_CODE,
        CD_PST = sel.First().CITY,
        NUM_STATUS_TAB = sel.Sum(s => s.STATUS_TAB != "OK" ? 1 : 0),
        SUM_AMOUNT = sel.Sum(s => (decimal?)s.AMOUNT)
    })
    
    //4. HAVING
    .Where(p => p.NUM_STATUS_TAB == 0)
    .ToList();

Step 4 and 5 have been swap because we are filtering on a field that is not present in the groupby, so we need the select in order to have it avaidable.

At Step 3. GROUP BY, notice the overload used. Order by has 9 overload, MS Docs. I will advice on using the simple one till you get familiar with it

Upvotes: 1

Related Questions