views:

1588

answers:

3

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
+2  A: 

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

Jon Skeet
Thanks Jon. One of the hardest parts about Linq so far is figuring out what you can and can't do. I'll definitely take a look at your Push technique once I get a bit more comfortable with the standard methodology.
Darrel Miller
+3  A: 

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);
David B
It is a bit manual but it is good enough for me. It is funny how when you break linq down you quickly return to just syntactic shortcuts for a foreach look.
Darrel Miller
Ironic then, isn't it? foreach is just a syntactic shortcut.
David B
A: 

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