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

Laurent Gabiot · Accepted Answer · 2021-11-01 13:29:48Z

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.

edited Nov 1, 2021 at 13:29

answered Nov 1, 2021 at 13:24

Laurent Gabiot

1,33112 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ArtiN · Accepted Answer · 2021-11-01 13:25:51Z

1

get clients, all of whose accounts is open:

var openAccountsByClient = accounts
    .Where(e => e.Status == "Open")
    .GroupBy(x => x.Client)
    .Select(y => new Client() {
       ...
    }).ToList();

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

var accountsByClientsWithOneAccount = accounts
    .GroupBy(x => x.Client)
    .Where(x => x.Any(e => e.Status == "Open"))
    .Select(y => new Client() {
       ...
    }).ToList();

answered Nov 1, 2021 at 13:25

ArtiN

111 bronze badge

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related