LINQ query with multiple aggregates

Question

How would I create the equivalent Linq To Objects query?

SELECT MIN(CASE WHEN p.type = "In" THEN p.PunchTime ELSE NULL END ) AS EarliestIn,
       MAX(CASE WHEN p.type = "Out" THEN p.PunchTime ELSE NULL END ) AS LatestOUt
FROM Punches p

Answer 1

Single enumeration yielding both min and max (and any other aggregate you want to throw in there). This is much easier in vb.net.

I know this doesn't handle the empty case. That's pretty easy to add.

    List<int> myInts = new List<int>() { 1, 4, 2, 0, 3 };
    var y = myInts.Aggregate(
        new { Min = int.MaxValue, Max = int.MinValue },
        (a, i) =>
        new
        {
           Min = (i < a.Min) ? i : a.Min,
           Max = (a.Max < i) ? i : a.Max
        });
    Console.WriteLine("{0} {1}", y.Min, y.Max);

Answer 2

You can't efficiently select multiple aggregates in vanilla LINQ to Objects. You can perform multiple queries, of course, but that may well be inefficient depending on your data source.

I have a framework which copes with this which I call "Push LINQ" - it's only a hobby (for me and Marc Gravell) but we believe it works pretty well. It's available as part of MiscUtil, and you can read about it in my blog post on it.

It looks slightly odd - because you define where you want the results to go as "futures", then push the data through the query, then retrieve the results - but once you get your head round it, it's fine. I'd be interested to hear how you get on with it - if you use it, please mail me at [email protected].