CODEWITHSUNDEEP
c#linq
Bastien Vandamme
Bastien Vandamme

Reputation: 18465

Linq get latest date (or maximum value) of each element

I have a table that contain the history of balance of account. I would like to write a Linq query that return the last balance of each account.

At this moment I use this query:

var lastDate = AccountBalances.Max(y => y.BalanceDate);

var listOfBalance = AccountBalances.Where(y => y.BalanceDate == lastDate).ToList();
listOfBalance.Dump(); // FYI I use LinqPad

But this is not corretc because I do not perform the calculation of each account every day. I can have a last balance datedifferent for each account.

Example:

AccountNumber   |  BalanceDate  |  Value
----------------------------------------
1234-0001       |  2017-08-08   |  100
1234-0002       |  2017-08-08   |  250
1234-0003       |  2017-08-08   |  500
1234-0004       |  2017-08-08   |  150
1234-0001       |  2017-08-09   |  110
1234-0002       |  2017-08-09   |  230
1234-0003       |  2017-08-09   |  400
1234-0001       |  2017-08-10   |  120
1234-0002       |  2017-08-10   |  210

My query return this

AccountNumber   |  BalanceDate  |  Value
----------------------------------------
1234-0001       |  2017-08-10   |  120
1234-0002       |  2017-08-10   |  210

But should return

AccountNumber   |  BalanceDate  |  Value
----------------------------------------
1234-0001       |  2017-08-10   |  120
1234-0002       |  2017-08-10   |  210
1234-0003       |  2017-08-09   |  400
1234-0004       |  2017-08-08   |  150

Upvotes: 1

Views: 297

Answers (1)

B.Balamanigandan
B.Balamanigandan

Reputation: 4875

You can try the following code,

void Main()
{
    List<LedgerAccount> account = new List<UserQuery.LedgerAccount>()
    {
        new LedgerAccount() { AccountNumber = "1234-0001", BalanceDate = new DateTime(2017, 8, 8), Value = 100 },
        new LedgerAccount() { AccountNumber = "1234-0002", BalanceDate = new DateTime(2017, 8, 8), Value = 250 },
        new LedgerAccount() { AccountNumber = "1234-0003", BalanceDate = new DateTime(2017, 8, 8), Value = 500 },
        new LedgerAccount() { AccountNumber = "1234-0004", BalanceDate = new DateTime(2017, 8, 8), Value = 150 },
        new LedgerAccount() { AccountNumber = "1234-0001", BalanceDate = new DateTime(2017, 8, 9), Value = 110 },
        new LedgerAccount() { AccountNumber = "1234-0002", BalanceDate = new DateTime(2017, 8, 9), Value = 230 },
        new LedgerAccount() { AccountNumber = "1234-0003", BalanceDate = new DateTime(2017, 8, 9), Value = 400 },
        new LedgerAccount() { AccountNumber = "1234-0001", BalanceDate = new DateTime(2017, 8, 10), Value = 120 },
        new LedgerAccount() { AccountNumber = "1234-0002", BalanceDate = new DateTime(2017, 8, 10), Value = 210 },
    };

    account.GroupBy(m => m.AccountNumber).Select(m => m.OrderByDescending(s => s.BalanceDate).FirstOrDefault()).Dump();
}

public class LedgerAccount
{
    public string AccountNumber { get; set; }
    public DateTime BalanceDate { get; set; }
    public int Value { get; set; }
}

The Linq Query for your requirement is

var result = account.GroupBy(m => m.AccountNumber)
        .Select(m => m.OrderByDescending(s => s.BalanceDate).FirstOrDefault())

Output:

AccountNumber    BalanceDate               Value
__________________________________________________
1234-0001        8/10/2017 12:00:00 AM       120 
1234-0002        8/10/2017 12:00:00 AM       210 
1234-0003        8/9/2017 12:00:00 AM        400 
1234-0004        8/8/2017 12:00:00 AM        150

Upvotes: 1

Related Questions