Code CSS

Thursday, February 16, 2012

GroupBy with Dynamic LINQ

Dynamic LINQ using System.Linq.Dynamic

First of all, when people talk about "Dynamic LINQ" there are any number of things they could be referring to. I want to mention a tidbit about the DynamicQuery sample that is delivered with Visual Studio. In Visual Studio 2010, the sample is in the following directory:

\Program Files (x86)\Microsoft Visual Studio 10.0\Samples\

Somewhere beneath that directory you will find the CSharpSamples.zip file, which contains a LinqSamples\DynamicQuery path with the dynamic querying capability I'm referring to.  I first learned about this from Scott Guthrie's blog.

The DynamicQuery example contains the Dynamic.cs file, which is a set of extension methods and an expression language that allow you to easily manipulate LINQ queries at runtime. This is incredibly powerful but is sparsely documented and introduces a few problems I'll discuss in later posts.

Group By with Dynamic LINQ

I had a really hard time finding examples of how to group results using Dynamic LINQ, so here's a simple example I came up with:

List<Data> datalist = new List<Data>();
datalist.Add(
    new Data() { Num1 = 1, Num2 = 2, Str1 = "A", Str2 = "Red" });
datalist.Add(
    new Data() { Num1 = 2, Num2 = 5, Str1 = "A", Str2 = "Blue" });
datalist.Add(
    new Data() { Num1 = 3, Num2 = 8, Str1 = "A", Str2 = "Red" });
datalist.Add(
    new Data() { Num1 = 4, Num2 = 12, Str1 = "B", Str2 = "Red" });

var test = datalist.AsQueryable()
    .GroupBy("new(Str1,Str2)", "new(Num2,Num1)");
    .Select("new(Key.Str1, Key.Str2, Average(Num2) as SumNum, Max(Num1) as MaxNum)");

foreach (object o in test)
    Console.WriteLine(o.ToString());
In the example, the "Data" class is just a class with three properties, Num1, Num2, Str1, and Str2 to simulate a table of data. I couldn't find documentation on the GroupBy method anywhere, so I hope this helps someone else.

4 comments:

  1. An other solution to do "dynamic" analysis query (= GroupBy + Aggregation) is to use the new .QueryByCube() function : www.adaptivelinq.com

    ReplyDelete
  2. how to round of the sum in the selection

    ReplyDelete