LINQ to Object - How to implement WHERE clause `If at least one of the elements is` for sub-group

Question

I have a type of non-ordered records:

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

and its instantiation is:

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" },
};

and output is:

+--------+---------+--------+
| Client | Account | Status |
+--------+---------+--------+
| 123    | 123.def | Open   |
+--------+---------+--------+
| 456    | 456.def | Closed |
+--------+---------+--------+
| 123    | 123.abc | Open   |
+--------+---------+--------+
| 789    | 789.abc | Open   |
+--------+---------+--------+
| 456    | 456.abc | Closed |
+--------+---------+--------+
| 789    | 789.ghi | Open   |
+--------+---------+--------+
| 789    | 789.def | Closed |
+--------+---------+--------+
| 789    | 789.jkl | Open   |
+--------+---------+--------+

After that, for further manipulation of the object, I enter the following additional types:

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; }
}

and doing the select-projection of rows grouped by Client-field:

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();

and I get the output:

+------+------------------+
| Code | Accounts         |
|      +---------+--------+
|      | Number  | Status |
+------+---------+--------+
| 123  | 123.def | Open   |
|      +---------+--------+
|      | 123.abc | Open   |
+------+---------+--------+
| 456  | 456.def | Closed |
|      +---------+--------+
|      | 456.abc | Closed |
+------+---------+--------+
| 789  | 789.abc | Open   |
|      +---------+--------+
|      | 789.ghi | Open   |
|      +---------+--------+
|      | 789.def | Closed |
|      +---------+--------+
|      | 789.jkl | Open   |
+------+---------+--------+

But, my question is: How can I implement WHERE-clause for filtered Accounts sub-group?

For example:

• How where-clause for logic: get clients, all of whose accounts is open for getting this?:

+------+------------------+
| Code | Accounts         |
|      +---------+--------+
|      | Number  | Status |
+------+---------+--------+
| 123  | 123.def | Open   |
|      +---------+--------+
|      | 123.abc | Open   |
+------+---------+--------+

• How where-clause for logic: get clients, who have at least one account is open? for getting this?:

+------+------------------+
| Code | Accounts         |
|      +---------+--------+
|      | Number  | Status |
+------+---------+--------+
| 123  | 123.def | Open   |
|      +---------+--------+
|      | 123.abc | Open   |
+------+---------+--------+
| 789  | 789.abc | Open   |
|      +---------+--------+
|      | 789.ghi | Open   |
|      +---------+--------+
|      | 789.def | Closed |
|      +---------+--------+
|      | 789.jkl | Open   |
+------+---------+--------+

Full interactive code listing at dotnetfiddle

Answer 1

Query for: get clients, all of whose accounts is open

var ClientsWithAllAccountsAsOpen = clients
   .Where(c => c.Accounts
       .All(a => a.Status == "Open"));
// Add ToList() or equivalent if you need to materialise.

Query for: get clients, who have at least one account is open

var ClientsWithAtLeastOneOpenAccounts = clients
   .Where(c => c.Accounts
       .Any(a => a.Status == "Open"));
// Add ToList() or equivalent if you need to materialise.