Linq select records closest to date

Question

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!

score 5 · Accepted Answer · edited May 23 '17 at 11:55

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#

+1 Seems to work so far thanks, just one error that was easily fixed: `The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead.` — JK., Feb 07 '11 at 00:35
@JK, it is linq-to-sql specifics, i was testing on linq-to-objects. — Snowbear, Feb 07 '11 at 06:53

Linq select records closest to date

1 Answers1

Linked