LINQ to Object如何为分组后的Accounts子集合实现WHERE条件过滤

1. 行记录类型Row #

public class Row {
    public string Client { get; set; }
    public string Account { get; set; }
    public string Status { get; set; }
}

2. 示例初始化数据 #

List<Row> accounts = new List<Row>() {
    new Row() { Client = "123", Account = "123.def", Status = "Open" },
    new Row() { Client = "456", Account = "456.def", Status = "Closed" },
    new Row() { Client = "123", Account = "123.abc", Status = "Open" },
    new Row() { Client = "789", Account = "789.abc", Status = "Open" },
    new Row() { Client = "456", Account = "456.abc", Status = "Closed" },
    new Row() { Client = "789", Account = "789.ghi", Status = "Open" },
    new Row() { Client = "789", Account = "789.def", Status = "Closed" },
    new Row() { Client = "789", Account = "789.jkl", Status = "Open" },
};

3. 业务对象类型定义 #

public class Client {
    public string Code { get; set; }
    public List<Account> Accounts { get; set; }
}

public class Account {
    public string Number { get; set; }
    public string Status { get; set; }
}

4. 现有基础分组投影代码 #

List<Client> clients = accounts
    .GroupBy(x => x.Client)
    .Select(y => new Client() {
        Code = y.Key,
        Accounts = y.GroupBy(z => z.Account)
            .Select(z => new Account() {
                Number = z.First().Account,
                Status = z.First().Status
            }).ToList()
    }).ToList();

场景1：筛选名下所有账户状态均为Open的客户 #

仅返回符合条件的Client及其全部账户信息，只需在分组后、投影前添加All匹配逻辑即可：

List<Client> clientsWithAllOpenAccount = accounts
    .GroupBy(x => x.Client)
    // 新增过滤条件：当前客户分组下所有行的Status都为Open
    .Where(y => y.All(z => z.Status == "Open"))
    .Select(y => new Client() {
        Code = y.Key,
        Accounts = y.GroupBy(z => z.Account)
            .Select(z => new Account() {
                Number = z.First().Account,
                Status = z.First().Status
            }).ToList()
    }).ToList();

查询结果：仅返回客户123及其两个Open状态的账户

场景2：筛选名下至少有一个账户状态为Open的客户 #

仅返回符合条件的Client及其全部账户信息，只需在分组后、投影前添加Any匹配逻辑即可：

List<Client> clientsWithAnyOpenAccount = accounts
    .GroupBy(x => x.Client)
    // 新增过滤条件：当前客户分组下至少存在一行的Status为Open
    .Where(y => y.Any(z => z.Status == "Open"))
    .Select(y => new Client() {
        Code = y.Key,
        Accounts = y.GroupBy(z => z.Account)
            .Select(z => new Account() {
                Number = z.First().Account,
                Status = z.First().Status
            }).ToList()
    }).ToList();

查询结果：返回客户123、789及其名下全部账户

内容的提问来源于stack exchange，提问作者timnavigate