Using Linq to create crosstab results

Question

Possible Duplicate:
Is it possible to Pivot data using LINQ?

I'm 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 that are causing my problems. Any solutions to this?

This question is asking about a pivot against unknown result columns. The proposed duplicate is a pivot against known result columns. These two situations are quite different. Voting to reopen. — Amy B, Dec 13 '12 at 15:46

Amy B · Answer 1 · 2008-11-26T15:29:08.930

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

score 1 · Answer 2 · edited Nov 21 '11 at 23:30

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()
                     }

A simple solution.. but it won't work where you don't know what value "Lab" will have. — Quango, Mar 01 '13 at 09:20

score 1 · Answer 3 · answered Nov 26 '08 at 14:59

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.

Can you please let me know why this was voted down? Where am I incorrect? — David, Dec 24 '08 at 20:07

score 1 · Answer 4 · answered Apr 01 '09 at 18:33

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.

Using Linq to create crosstab results

4 Answers4

Linked