Linq语法，带有group by和having子句

E

埃里克·布莱恩

成员
已加入
2014年6月15日
留言内容
8
编程经验
3-5
大家好，
我尝试使用具有having子句的linq语法从数据表中获取列表。
但是我没有得到我想要的。
这是SQL语法：

SQL: 
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

这是我的linq语法：

C#: 
            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();


你有想法吗 ？

非常感谢。

埃里克
 
Sort by date Sort by votes
跳伞

跳伞

工作人员
已加入
2019年4月6日
留言内容
2,538
地点
弗吉尼亚州切萨皮克
编程经验
10+
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.
 
投票 0 下注
跳伞

跳伞

工作人员
已加入
2019年4月6日
留言内容
2,538
地点
弗吉尼亚州切萨皮克
编程经验
10+
这是因为在C＃的18-22行中，您仅创建仅具有这两个字段的组成员。
 
投票 0 下注
跳伞

跳伞

工作人员
已加入
2019年4月6日
留言内容
2,538
地点
弗吉尼亚州切萨皮克
编程经验
10+
您不必再次提取数据。它作为lambda参数之一传递给您。
 
投票 0 下注
跳伞

跳伞

工作人员
已加入
2019年4月6日
留言内容
2,538
地点
弗吉尼亚州切萨皮克
编程经验
10+
我是反SQL的，所以要花大量精力。看起来帖子＃中的SQL可以简化为：
SQL: 
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.
 
投票 0 下注
跳伞

跳伞

工作人员
已加入
2019年4月6日
留言内容
2,538
地点
弗吉尼亚州切萨皮克
编程经验
10+
无论如何，这似乎给了我期望看到的结果：

C#: 
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.
Using Any(): 
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")
            };
    });

Using All(): 
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")
            };
    });
 
Last edited:
投票 0 下注
最佳 底部