问题描述
如何使用Linq选择最接近指定日期的记录?这用于具有日期，产品ID，位置ID和余额的交易表.
How do you use Linq to select the record that is closest to a specified date? This is for a transaction table that has a date, product id, location id, and balance.
鉴于这些要求:
- 如果指定的日期有几笔交易，请选择当天的最后一笔交易
- 如果指定的日期没有交易，请选择最接近的先前交易
- 在多个位置(例如仓库)显示余额-每个仓库将进行单独的交易
- 显示多种产品的余额-每个产品将有单独的交易
表格数据:
// code
Id, TransDateTime, ProductId, WarehouseId, Balance
1, 1-Jan-2011 00:00, 1, 1, 100
2, 1-Jan-2011 00:00, 1, 2, 10
3, 2-Jan-2011 00:00, 1, 1, 150
4, 3-Jan-2011 00:00, 1, 2, 25
5, 3-Jan-2011 00:00, 2, 1, 333
6, 7-Jan-2011 00:00, 1, 1, 149
7, 7-Jan-2011 01:00, 1, 2, 30
8, 7-Jan-2011 02:00, 1, 2, 35
测试日期和输出
Date: 1-Jan would output:
1, 1-Jan-2011 00:00, 1, 1, 100
2, 1-Jan-2011 00:00, 1, 2, 10
Date: 3-Jan would output:
3, 2-Jan-2011 00:00, 1, 1, 150
4, 3-Jan-2011 00:00, 1, 2, 25
5, 3-Jan-2011 00:00, 2, 1, 333
// product 1, warehouse 1 wasn't sold on the 3rd
// so the row from 2-Jan is returned
Date: 7-Jan would output:
5, 3-Jan-2011 00:00, 2, 1, 333
6, 7-Jan-2011 00:00, 1, 1, 149
9, 7-Jan-2011 02:00, 1, 2, 35
// product 2, warehouse 1 wasn't sold on the 7th
// so the row from 3-Jan is returned
// product 1, warehouse 2 was sold twice on the 7th
// so the later one is used
我认为这将需要对分组进行分组(产品->仓库->日期)或类似的分组.这超出了我的能力！
I think it's going to require grouping of groups (product -> warehouse -> date) or similar. Its beyond my linq ability!
推荐答案
步骤:
1)筛选出inputDate
之后发生的交易2)按产品和仓库对其余交易进行分组
3)在每个组中找到最近的交易
4)格式化结果对象
Steps:
1) Filter out transactions that happened after inputDate
2) Group rest of transactions by product and warehouse
3) In each group find most recent transaction
4) Format result object
直接实现:
DateTime inputDate = ...;
var result = transactions
.Where(t => t.TransDateTime.Date <= inputDate.Date)
.GroupBy(t => new {t.ProductId, t.WarehouseId})
.Select(x => new {
x.Key,
LastTransaction = x.OrderByDescending(t => t.TransDateTime).First(),
})
.Select(x => new {
Id = x.LastTransaction.Id,
Date = x.LastTransaction.TransDateTime,
ProductId = x.Key.ProductId,
WarehouseId = x.Key.WarehouseId,
Balance = x.LastTransaction.Balance,
});
如果要进行一些优化，可以考虑为IEnumerable实现MaxBy扩展方法来代替x.OrderByDescending(t => t.TransDateTime).First().如果您有很多事务，因为它是O(n)而不是O(n log n)，它将提高性能. MaxBy实现可以在这里使用，例如: C#中的简单LINQ问题
If you want some optimizations you can consider implementing MaxBy extension method for IEnumerable to replace x.OrderByDescending(t => t.TransDateTime).First(). It will improve performance if you have many transactions since it is O(n) instead of O(n log n). MaxBy implementation can be taken here for example: Simple LINQ question in C#
这篇关于Linq选择最接近日期的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！