Linq syntax with group by and having clauses

[eric.bryan]

Hello everybody,
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();

Do you have an idea ?

Thanks a lot in advance.

Eric.

 

[Skydiver]

I suggest looking at the All() LINQ extension method. All that line 14 is looking for is that not one item in the group has STATUS_TAB set to OK.

 

[eric.bryan]

I suggest looking at the All() LINQ extension method. All that line 14 is looking for is that not one item in the group has STATUS_TAB set to OK.

Thanks for your reply.
My problem is that with the linq syntax I get only 2 fields in the list : SUM_AMOUNT and NUM_STAUS_TAB.

 

[Skydiver]

That's because in your C#'s lines 18-22, you are only creating group members that just have those two fields.

 

[eric.bryan]

That's because in your C#'s lines 18-22, you are only creating group members that just have those two fields.

But it's the way I want it to work.
I cannot put all the fields of the Select block into the GroupBy block, it would be another request which won't suit my need.

 

[Skydiver]

You don't have to pull the data again. It's passed in to you as one of the lambda parameters.

 

[Skydiver]

I'm anti-SQL, so take this with a huge grain of salt. It looks like the SQL in post # can be simplified to:

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' AND NUM_STATUS_TAB = 0
GROUP BY ID_BEN, GAR1

since your HAVING clause is computing the same thing that NUM_STATUS_TAB is computing.

 

[Skydiver]

Anyway, this seems to give me the results I expected to see:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;

class Client
{
    public int IdBen { get; }
    public int Gar1 { get; }
    public string FirstName { get; }
    public string LastName { get; }
    public string Adr1 { get; }
    public string Adr2 { get; }
    public string ZipCode { get; }
    public string City { get; }
    public int Amount { get; }
    public string StatusTab { get; }
    public string StatusEnr { get; }
    public string StateEnr { get; }

    public Client(int idBen,
                  int gar1,
                  string firstName,
                  string lastName,
                  string adr1,
                  string adr2,
                  string zipCode,
                  string city,
                  int amount,
                  string statusTab,
                  string statusEnr,
                  string stateEnr)
    {
        IdBen = idBen;
        Gar1 = gar1;
        FirstName = firstName;
        LastName = lastName;
        Adr1 = adr1;
        Adr2 = adr2;
        ZipCode = zipCode;
        City = city;
        Amount = amount;
        StatusTab = statusTab;
        StatusEnr = statusEnr;
        StateEnr = stateEnr;
    }

    public static IEnumerable<Client> GetValues()
    {
                                                                          // Tab   Enr   StateEnr
        yield return new Client(1, 1, "F1", "L1", "A1", "B1", "Z1", "C1",  1, "OK", "OK", "1");
        yield return new Client(1, 1, "F2", "L2", "A2", "B2", "Z2", "C2",  2, "OK", "  ", "1");    // should be skipped
        yield return new Client(1, 1, "F3", "L3", "A3", "B3", "Z3", "C3",  3, "OK", "OK", "0");    // should be skipped
        yield return new Client(1, 1, "F4", "L4", "A4", "B4", "Z4", "C4",  4, "OK", "OK", "1");

        // All these should be skipped because the group will have a StatusTab that is not set to "OK"
        yield return new Client(1, 2, "F5", "L5", "A5", "B5", "Z5", "C5",  5, "OK", "OK", "1");
        yield return new Client(1, 2, "F6", "L6", "A6", "B6", "Z6", "C6",  6, "OK", "  ", "1");    // should be skipped
        yield return new Client(1, 2, "F7", "L7", "A7", "B7", "Z7", "C7",  7, "OK", "OK", "0");    // should be skipped
        yield return new Client(1, 2, "F8", "L8", "A8", "B8", "Z8", "C8",  8, "OK", "OK", "1");
        yield return new Client(1, 2, "F9", "L8", "A9", "B9", "Z9", "C9",  9, "  ", "OK", "1");    // StatusTab != "OK"

        yield return new Client(1, 3, "FA", "LA", "AA", "BA", "ZA", "CA", 10, "OK", "OK", "1");
        yield return new Client(1, 3, "FB", "LB", "AB", "BB", "ZB", "CB", 11, "OK", "  ", "1");    // should be skipped
        yield return new Client(1, 3, "FC", "LC", "AC", "BC", "ZC", "CC", 12, "OK", "OK", "0");    // should be skipped
        yield return new Client(1, 3, "FD", "LD", "AD", "BD", "ZD", "CD", 13, "OK", "OK", "1");
    }
}

class Program
{
    static void Main()
    {
        var items = Client.GetValues()
                          .Where(c => c.StatusEnr == "OK" && c.StateEnr == "1")
                          .GroupBy(c => new { c.IdBen, c.Gar1 })
                          .Select(g => {
                              var first = g.First();
                              return new
                              {
                                  g.Key.IdBen,
                                  g.Key.Gar1,
                                  first.FirstName,
                                  first.LastName,
                                  first.Adr1,
                                  first.Adr2,
                                  first.ZipCode,
                                  first.City,
                                  SumAmount = g.Sum(c => c.Amount),
                                  NumStatusTab = g.Count(c => c.StatusTab != "OK")
                              };
                          })
                          .Where(c => c.NumStatusTab == 0);

        foreach (var item in items)
        {
            Console.WriteLine($"{item.IdBen} {item.Gar1} {item.FirstName} {item.LastName} " +
                              $"{item.Adr1} {item.Adr2} {item.ZipCode} {item.City} " +
                              $"{item.SumAmount} {item.NumStatusTab}");
        }
    }
}

Lines 79-94 takes the group and finds the first entry (to correspond to all the FIRST() in your SQL, as well as computes the SumAmount as well as count how many have StatusTab not set to "OK".

Line 95 applies the condition of your HAVING clause or the equivalent as I noted in post #7.

I wrote the above that way instead of using All() or Any() to try to be efficient about traversing the data.

Spoiler

var items = Client.GetValues()
    .Where(c => c.StatusEnr == "OK" && c.StateEnr == "1")
    .GroupBy(c => new { c.IdBen, c.Gar1 })
    .Where(g => !g.Any(c => c.StatusTab != "OK"))
    .Select(g => {
        var first = g.First();
        return new
        {
            g.Key.IdBen,
            g.Key.Gar1,
            first.FirstName,
            first.LastName,
            first.Adr1,
            first.Adr2,
            first.ZipCode,
            first.City,
            SumAmount = g.Sum(c => c.Amount),
            NumStatusTab = g.Count(c => c.StatusTab != "OK")
            };
    });

var items = Client.GetValues()
    .Where(c => c.StatusEnr == "OK" && c.StateEnr == "1")
    .GroupBy(c => new { c.IdBen, c.Gar1 })
    .Where(g => g.All(c => c.StatusTab == "OK"))
    .Select(g => {
        var first = g.First();
        return new
        {
            g.Key.IdBen,
            g.Key.Gar1,
            first.FirstName,
            first.LastName,
            first.Adr1,
            first.Adr2,
            first.ZipCode,
            first.City,
            SumAmount = g.Sum(c => c.Amount),
            NumStatusTab = g.Count(c => c.StatusTab != "OK")
            };
    });