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 initialization 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" },
};
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
CodePudding user response:
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();
CodePudding user response:
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.