Linq query to do group by and min

Question

I have a datastructure as shown below. For each (Person, Game) pairs, I need to find the lastest score in the past 24 hours. Is it possible to do that in LINQ? Something like (Person, Game, LatestScore)

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

Any hints would be very helpful.

Mithrandir · Accepted Answer · 2014-08-06T10:05:46.547

Assuming you have a class like this:

    class GameInfo
    {

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

You could do:

        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);
        }

As @Rawling pointed out, getting the scoce can be quite costly if you have lage data sets. So doing that efficiently might save alot of time in getting the desired output.

@Rawling: I like the Aggregate solution you pointed out! Thanks! — Mithrandir, Aug 06 '14 at 10:06

score 1 · Answer 2 · answered Aug 06 '14 at 09:26

1

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()
    });

answered Aug 06 '14 at 09:26

Giannis Paraskevopoulos

18,261
1
49
69

There are [better ways](http://stackoverflow.com/questions/914109/how-to-use-linq-to-select-object-with-minimum-or-maximum-property-value) of getting "Max By" than the `O(n^2)` "where equals max". – Rawling Aug 06 '14 at 09:49

Linq query to do group by and min

2 Answers2