本文是小编为大家收集整理的关于Linq选择最接近日期的记录的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
如何使用 Linq 选择最接近指定日期的记录?这是针对具有日期、产品 ID、位置 ID 和余额的事务表.
鉴于这些要求:
- 如果指定日期有多个交易，则选择当天的最后一笔交易
- 如果在指定日期没有交易，则选择最接近的先前交易
- 在多个位置(例如仓库)显示余额 - 每个仓库都有单独的交易
- 显示多个产品的余额 - 每个产品都有单独的交易
表格数据:
// 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
我认为这将需要对组进行分组(产品 -> 仓库 -> 日期)或类似的.它超出了我的 linq 能力！
推荐答案
步骤:
1) 过滤掉 inputDate
之后发生的事务2) 按产品和仓库对剩余交易进行分组
3) 在每个组中查找最近的交易
4) 格式化结果对象
简单的实施:
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 实现可以在这里例如:Simple LINQ question in C#
问题描述
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.
Given these requirements:
- Select the last transaction of the day if there were several on the specified day
- Select the closest prior transaction if there were none on the specified day
- Show the balance in multiple locations (eg warehouses) - each warehouse will have separate transactions
- Show the balance for multiple products - each product will have separate transactions
Table data:
// 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
Test dates and outputs
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!
推荐答案
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
Straightforward implementation:
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,
});
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#
更多相关问答
联系站长