Linq选择最接近日期的记录[英] Linq select records closest to date

本文是小编为大家收集整理的关于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#

本文地址:https://www.itbaoku.cn/post/1557160.html

问题描述

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#