Linq获取每个元素的最近日期（或最大值）

问题描述：

我有一个包含帐户余额历史的表。我想编写一个返回每个账户最后余额的Linq查询。Linq获取每个元素的最近日期（或最大值）

此时此刻我使用此查询：

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

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

但这并不是corretc因为我不执行每个计算每天帐户。每个账户可以有不同的最后余额。

例子：

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

我的查询返回此

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

但应该返回

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

答

你可以试试下面的代码，

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

LINQ查询您的要求是

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

输出：

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

Linq获取每个元素的最近日期（或最大值）

相关推荐