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

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].

Answer 2

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 3

It is possible to do multiple aggregates with LINQ-to-Objects, but it is a little ugly.

var times = punches.Aggregate(
    new { EarliestIn = default(DateTime?), LatestOut = default(DateTime?) },
    (agg, p) => new {
        EarliestIn = Min(
            agg.EarliestIn,
            p.type == "In" ? (DateTime?)p.PunchTime : default(DateTime?)),
        LatestOut = Max(
            agg.LatestOut,
            p.type == "Out" ? (DateTime?)p.PunchTime : default(DateTime?)) 
    }
);

You would also need Min and Max functions for DateTime since these are not available standard.

public static DateTime? Max(DateTime? d1, DateTime? d2)
{
    if (!d1.HasValue)
        return d2;
    if (!d2.HasValue)
        return d1;
    return d1.Value > d2.Value ? d1 : d2;
}
public static DateTime? Min(DateTime? d1, DateTime? d2)
{
    if (!d1.HasValue)
        return d2;
    if (!d2.HasValue)
        return d1;
    return d1.Value < d2.Value ? d1 : d2;
}