Linq查询做组by和min

本文关键字：min by 查询 Linq | 更新日期: 2023-09-27 18:16:48

我的数据结构如下所示。对于每个(Person, Game)配对，我需要找到过去24小时内的最新分数。有可能在LINQ中做到这一点吗?比如(Person, Game, LatestScore)

+----------------+-----------------+---------------+-+------------+
| Person         |  Game           | Score         |EventTime     |
+-----------------------------------------------------------------+
|                |                 |               |              |
|                |                 |               |              |
+----------------+-----------------+---------------+--------------+

Linq查询做组by和min

假设您有这样一个类:

    class GameInfo
    {
        public DateTime EventTime { get; set; }
        public String Game { get; set; }
        public String Person { get; set; }
        public double Score { get; set; }
    }

你可以这样做:

        List<GameInfo> data = new List<GameInfo> { 
        new GameInfo { Game = "G1", Person = "A", Score = 10, EventTime = new DateTime(2014, 10, 10, 10, 10, 10) },
        new GameInfo { Game = "G1", Person = "A", Score = 10, EventTime = new DateTime(2014, 10, 10, 10, 10, 10) },
        new GameInfo { Game = "G2", Person = "B", Score = 11, EventTime = new DateTime(2014, 10, 10, 20, 10, 10) },
        new GameInfo { Game = "G2", Person = "B", Score = 11, EventTime = new DateTime(2014, 10, 10, 20, 10, 10) }
    };
        var q = from game in data
                group game by new { G = game.Game, P = game.Person } into g
                select new {
                    Person = g.Key.P,
                    Game = g.Key.G,
                    Score = g.Aggregate((curmin, x) => (curmin == null || (x.EventTime) < curmin.EventTime ? x : curmin)).Score
                };
        foreach (var item in q)
        {
            Console.WriteLine("{0}, {1}, {2}", item.Game, item.Person, item.Score);
        }

正如@Rawling所指出的，如果你有大量的数据集，获得分数可能会非常昂贵。因此，这样做可以有效地节省大量时间，以获得所需的输出。

var dt = DateTime.Now.AddDays(-1);
var results = context.Where(x=>x.EventTime >= dt)
    .GroupBy(x=>new {x.Person,x.Game})
    .Select(x=>new 
    {
        x.Key.Person,
        x.Key.Game,
        LatestScore = x.Where(d=>d.EventTime == x.Max(l=>l.EventTime))
                       .Select(d=>d.Score)
                       .FirstOrDefault()
    });
相关文章：