Using Linq to create crosstab results

Question

Hi, Im wondering if its at all possible to create crosstab style results with Linq. I have some data that looks like the following:

    var list = new[]
    {
        new {GroupId = 1, Country = "UK", Value = 10},
        new {GroupId = 1, Country = "FR", Value = 12},
        new {GroupId = 1, Country = "US", Value = 18},
        new {GroupId = 2, Country = "UK", Value = 54},
        new {GroupId = 2, Country = "FR", Value = 55},
        new {GroupId = 2, Country = "UK", Value = 56}
    };

and I'm trying to output to a repeater control something like the following:

GroupId.....UK.....FR.....US
1...........10.....12.....18
2...........54.....55.....56

Its the dynamic columns thats causing my problems. Any solutions to this?

Regards

Mark

Answer 1

After doing a quick search you might want to look at the ModuleBuilder, TypeBuilder, and FieldBuilder classes in System.Reflection.Emit. They allow you to create a class dynamically at runtime. Outside of that you would need to do grouping on your objects and then do something with the hierarchical results you get from LINQ. I am not sure of a way to dynamically create anonymous type fields at runtime, and that sounds like what would need to happen.

Answer 2

You need a runtimy class to hold these runtimy results. How about xml?

XElement result = new XElement("result",
  list.GroupBy(i => i.GroupId)
  .Select(g =>
    new XElement("Group", new XAttribute("GroupID", g.Key),
      g.Select(i => new XAttribute(i.Country, i.Value))
    )
  )
);

Are you expecting multiple records per result cell? If so there would need to be some Summing (and more grouping) in there.

(this answer is proof of concept, not final result. There's several issues to address, such as: ordering of columns, missing cells, and so on).

Answer 3

Have you seen this question: http://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq

I am also searching for a solution to this problem and this is the best I have come up with so far.

Answer 4

You could try using the dynamic linq library provided by MS. They have a number of overloads to extensions methods that take strings as arguments. They also have an expression parser that takes a string an emits a lambda expression. You should be able to create a dynamic select using them.

A word of warning though, you end up with a non-generic IQueryable rather than a generic IQueryable so you are a little bit limited on what you can do with the result, and you give up a bit of type safety, but that may be OK in your application...

The link for the dynamic linq stuff is

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

There is a link where you can download the source code the the dynamic library, plus some nice illustrations of how you can use it.

Answer 5

var labResults = from lab in CoreLabResults where lab.Patient == 8 group lab by new { lab.Patient, lab.TestNo, lab.CollectedDate } into labtests select new { labtests.Key.Patient, labtests.Key.TestNo, labtests.Key.CollectedDate, MCHC = labtests.Where(lab => lab.TestVar == "MCHC").FirstOrDefault().Result, LYABS = labtests.Where(lab => lab.TestVar == "LYABS").FirstOrDefault().Result, TotalTests = labtests.Count() }