如何从项目列表中查找最小日期

Question

我有一个这样的表结构

id  |  itemId |  date |
1   |  a1     | 6/14/2015
2   |  a1     | 3/14/2015
3   |  a1     | 2/14/2015
4   |  b1     | 6/14/2015
5   |  c1     | 6/14/2015

从这个表结构中，我试图获取所有具有最小日期的不同项目。 例如，我正在尝试获取id = 3,4和5。

我尝试过遵循代码，但我不能

var items = (from i in _db.Items
             //where min(i.date) // doesn't seem right
             group i by i.itemID
             into d select new
             {
                 iId = d.Key,
             }).Distinct();

Answer 1

鉴于您的示例数据，我会这样做：

var query =
    from i in _db.Items
    group i by i.itemId into gis
    let lookup = gis.ToLookup(x => x.date, x => x.id)
    from x in lookup[gis.Min(y => y.date)]
    select x;

Answer 2

var items = from i in _db.Items
            group i.date by i.itemID
            into d select new {
                iId = d.Key, iDate = d.Min()
            };