sql-server - LINQ 中的分组/子查询到具有相关未分组列的实体

Question

我在数据库中有这张表:

ID    | Timestamp           | Score
------|---------------------|-------------------
1     | 2013-01-01 12:00:00 | 15
1     | 2013-01-02 11:00:00 | 1
1     | 2013-01-03 16:00:00 | 4
2     | 2013-01-08 04:00:00 | 7
2     | 2013-01-09 08:00:00 | 9
2     | 2013-01-10 11:00:00 | 6
3     | 2013-01-03 12:00:00 | 14
3     | 2013-01-01 10:00:00 | 15
3     | 2013-01-02 00:00:00 | 17

我需要的是每个 ID 的最后得分。换句话说，对于每个 ID，我需要选择具有最高时间戳的行，然后从该行获取分数。

我设法在 SQL Server 中做到了这一点(这可能不是最佳的)

SELECT ID, Timestamp, Score FROM Data cd
LEFT OUTER JOIN
    (SELECT ID as SubId, MAX(Timestamp) AS TS 
     FROM Data GROUP BY ID) AS SUB
ON cd.ID = SUB.SubId
WHERE Timestamp = TS

我需要一个 LINQ to Entities (v4) 解决方案来应对这种情况，但我不确定在这种情况下如何处理子查询和分组。

@Edit(回复评论):我试过类似下面的方法，但我不知道如何从该分组项目中获取分数，或者如何将子查询集成到其中。

from d in Datas
group d by d.ID into group
select new { ID = group.Key, TS = group.Max(i => i.Timestamp) }

这种情况可能吗？有没有更好的解决方案(性能方面)？

Answer 1

这应该可以实现您想要的:

from d in Datas
group d by d.ID into g
let maxDate = g.Max(d => d.Timestamp) 
select new 
{
    ID = g.Key, 
    Score = g.First(item => item.Timestamp == maxDate).Score
};

这是我在 LINQPad 中使用的完整代码 list 的副本:

void Main()
{
    var testData = GenerateTestData();

    var result = 
        from d in testData
        group d by d.ID into g
        let maxDate = g.Max(d => d.Timestamp) 
        select new 
        {
            ID = g.Key, 
            Score = g.First(item => item.Timestamp == maxDate).Score
        };

    foreach (var item in result)
    {
        Console.WriteLine("ID={0}; Score={1}", item.ID, item.Score);
    }
}

List<Item> GenerateTestData()
{
    List<Item> list = new List<Item>();
    list.Add(new Item(1, "2013-01-01 12:00:00", 15));
    list.Add(new Item(1, "2013-01-02 11:00:00", 1));
    list.Add(new Item(1, "2013-01-03 16:00:00", 4));
    list.Add(new Item(2, "2013-01-08 04:00:00", 7));
    list.Add(new Item(2, "2013-01-09 08:00:00", 9));
    list.Add(new Item(2, "2013-01-10 11:00:00", 6));
    list.Add(new Item(3, "2013-01-03 12:00:00", 14));
    list.Add(new Item(3, "2013-01-01 10:00:00", 15));
    list.Add(new Item(3, "2013-01-02 00:00:00", 17));
    return list;
}

class Item
{
    public Item(int id, string timestamp, int score)
    {
        ID = id;
        Timestamp = DateTime.Parse(timestamp);
        Score = score;
    }
    public int ID;
    public DateTime Timestamp;
    public int Score;
}

产生了输出:

ID=1; Score=4
ID=2; Score=6
ID=3; Score=14