How do I generate pivot data using LINQ?

Question

Before closing this as duplicate, please note that I am aware that there are many questions on "[c#] [linq] pivot" and I have spent the whole day trying to fix this and only now have turned to SO.

I get data in this form, from the database:

Item    Collection_Period   Value
====    =================   =====
Item3       201307          27.2
Item4       201308          19
Item3       201209          2.1
Item2       201307          345
Item1       201309          13.11
Item2       201308          34
Item3       200609          85
Item4       201308          58.2
Item3       201209          2.4
Item2       201309          12.1
Item1       201209          12.3

I need to manipulate data into this format:

Item    CurrMon-3   CurrMon-2   CurrMon-1
=====   =========   =========   =========
Item1                           13.11
Item2   345         34          12.1
Item3   27.2
Item4   19          58.2

(only last three months data needs to be shown). I am trying this:

var pivoted = new List<PivotedMeanData>();
var thisMonth = DateTime.Now.Month;
var p = meanData
    .GroupBy(i => i.Description)
    .Select(g => new PivotedMeanData
    {
        Description = g.Key,
        M3 = g.Where(c => DateTime.ParseExact(c.CollectionPeriod, "yyyyMM", CultureInfo.InvariantCulture).Month == thisMonth - 3).ToString().Select(c => c.Value),
        M2 = g.Where(c => DateTime.ParseExact(c.CollectionPeriod, "yyyyMM", CultureInfo.InvariantCulture).Month == thisMonth - 2).ToString().Select(c => c.Value),
        M1 = g.Where(c => DateTime.ParseExact(c.CollectionPeriod, "yyyyMM", CultureInfo.InvariantCulture).Month == thisMonth - 1).ToString().Select(c => c.Value)
    });
return pivoted;

I have a class to hold this data:

public class PivotedMeanData
{
    public string Description { get; set; }
    public string M3 { get; set; }
    public string M2 { get; set; }
    public string M1 { get; set; }
}

The definition of MeanData class:

public class MeanData
{
    public string Description { get; set; }
    public long SeqNo { get; set; }
    public string CollectionPeriod { get; set; }
    public long Value { get; set; }
}

I searched a lot and found this question to be the exact match to my challenge. However, if I add the .Select(c => c.Value) (since I just need the value for that period) at the end of Where predicate the code is not compiling.

'char' does not contain a definition for 'Value'

The referred question is showing the exact same thing (they just used sum instead of select)

What am I doing wrong here? Is my attempt completely wrong or just the getting the Value is wrong?

score 3 · Accepted Answer · answered Oct 18 '13 at 13:03

Because you are calling Select() on a string which is enumerating the characters within that string:

M3 = g.Where(c => DateTime.ParseExact(c.CollectionPeriod, 
                                      "yyyyMM", 
                                      CultureInfo.InvariantCulture)
                          .Month == thisMonth - 3)
      .ToString()      // string
      .Select(c => c.Value)  //c = char

I suspect you want

M3 = g.Where(c => DateTime.ParseExact(c.CollectionPeriod, 
                                      "yyyyMM", 
                                      CultureInfo.InvariantCulture)
                          .Month == thisMonth - 3)
      .Sum(c => c.Value)

Though I guessed the answer from Raphael's answer, I liked your explanation. — Animesh, Oct 18 '13 at 13:32

score 2 · Answer 2 · answered Oct 18 '13 at 12:59

2

have you tried to do something like this?

g.Where(c => DateTime.ParseExact(c.CollectionPeriod, "yyyyMM", CultureInfo.InvariantCulture).Month == thisMonth - 3).Select(c => c.Value).FirstOrDefault();

answered Oct 18 '13 at 12:59

Raphael

1,677
1
15
23

This didn't work for me, but helped me in reaching the answer. I tried `g.Where(predicate).FirstOrDefault().Value.ToString()` and it worked. – Animesh Oct 18 '13 at 13:34
Sorry, of course you had to specify the field after firstOrDefault. Have you tried to do it without ToString(), the cast should not be necessary. – Raphael Oct 18 '13 at 13:52
My bad, I forgot to change the class definition. The M3, M2 and M1 are strings actually. Thanks for pointing this out. I edited the question. – Animesh Oct 18 '13 at 13:58

How do I generate pivot data using LINQ?

2 Answers2