Tech Tock

Time is of the essence.

LINQ Group By With Multiple Aggregates

It appears that LINQ does not support a query that outputs multiple group by aggregate fields.  For example, there’s Min() and a Max() functions, so to get both min & max from a list, you seem to need to make 2 LINQ queries:

 var min = (from d in data select d).Min();
 var max = (from d in data select d).Max();

In SQL, the 2 values would pop out easily from a single query:

 Select min(d), max(d) from data

LINQ has all the power of SQL and works similarly, just the syntax is different.  To achieve this result we have to use a group by.  But we’ll be grouping by nothing, including all items in the set in a single bucket. I usually group by “1”, which makes a single grouping of all the elements (grouping by any single object will do the same):

 var minMax = from d in values
    group d by 1 into g
    select new { Min = g.Min(x => x),
     Max = g.Max(x => x) };

 

minMax.First() now contains the min and max for the set of values.

Advertisements

November 18, 2009 - Posted by | Uncategorized | , , , , , , , , ,

7 Comments »

  1. If you needed to use those values later in the expression, you could use the “let” keyword in there, as well.

    Comment by Daniel Simon | November 19, 2009 | Reply

  2. In fact, this prints “1 5″ and is I think a bit more readable:

    var nums = new[] { 1, 2, 3, 4, 5 };
    var minMax = (from num in nums
    let max = nums.Max()
    let min = nums.Min()
    select new { Maximum = max, Minimum = min }).First();
    Console.WriteLine(minMax.Minimum.ToString() + ” ” + minMax.Maximum.ToString());

    Comment by Daniel Simon | November 19, 2009 | Reply

    • Hey wait a minute.

      Isn’t this the same as:

      var x = new { Maximum = nums.Max(), Minimum = nums.Min()};

      I don’t know about my query or the optimizer, but this is still 2 linq queries.

      If you had to do type conversion or something more complex, would it still be so simple?

      Comment by goldmanalpha | November 24, 2009 | Reply

      • Pretty much so…. His version can be rewritten as:
        var nums = new[] { 1, 2, 3, 4, 5 };
        var one = new[] {1};
        var minMax = (from x in one
        let max = nums.Max()
        let min = nums.Min()
        select new { Maximum = max, Minimum = min }).First();

        Actually, his is way worse, since those two LINQ queries are in the middle of a pointless loop. i.e., We have an O(2N) solution; we’re looking for an O(N) solution, and he gives us an O(2N^2) algorithm……

        Comment by James Curran | November 27, 2009

  3. Wow, I wasn’t aware of the let keyword for LINQ.

    Nice example.

    I’ve started to get comfortable with the group by, but I could get used to let.

    Thanks.

    Comment by goldmanalpha | November 19, 2009 | Reply

    • Well, here it is…. The public API is pretty, even if the implementation is rather last century….

      var nums = new[] { 1, 2, 3, 4, 5 };
      Console.WriteLine(nums.MinMax());

      class MinMax
      {
      public int Min {get; set;}
      public int Max {get; set;}
      public override string ToString()
      {
      return string.Format(“Min:{0}, Max:{1}”, Min, Max);
      }
      }

      static class Util
      {
      public static MinMax MinMax(this IEnumerable nums)
      {
      var mm = new MinMax {Min = Int32.MaxValue, Max = Int32.MinValue};
      foreach(var n in nums)
      {
      mm.Min = Math.Min(mm.Min, n);
      mm.Max = Math.Max(mm.Max, n);
      }
      return mm;
      }
      }

      I’m still think about a generic version……

      Comment by James Curran | November 27, 2009 | Reply

  4. I’m working on a new post on this topic, basically, I think this is the best you can do here:

    var x = new { Maximum = nums.Max(), Minimum = nums.Min()};

    Getting pretty busy, so it may be a while for me to explain why.

    Comment by goldmanalpha | November 27, 2009 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: