CODEWITHSUNDEEP
c#linq
Animesh D
Animesh D

Reputation: 5002

Pivoting data using LINQ

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 in this way:

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:

public List<PivotedMean> AssociateResultsWithCollectionDate(List<MeanData> meanData)
{
    var pivoted = new List<PivotedMean>();
    var currentMonth = DateTime.Now.Month;
    var results = meanData
        .GroupBy(i => i.Description)
        .Select(g => new
        {
            Description = g.Key,
            month3 = g.Where(c => c.CollPeriod.Month == currentMonth - 3),
            month2 = g.Where(c => c.CollPeriod.Month == currentMonth - 2),
            month1 = g.Where(c => c.CollPeriod.Month == currentMonth - 1)
        });
    return pivoted;
}

I have a class to hold this data:

public class PivotedMean
{
    public string Description { get; set; }
    public long Month3 { get; set; }
    public long Month2 { get; set; }
    public long Month1 { get; set; }
}

Though the PivotedMean class seems in alignment with the Linq query output, I get an error when I replace var results = with pivoted =.

Upvotes: 1

Views: 496

Answers (1)

Benjamin Gruenbaum
Benjamin Gruenbaum

Reputation: 276306

That's because pivoted is a list of PivotedMean and a LINQ query (in your case) returns an IEnumerable of an anonymous class.

  • You can add .ToList() at the end of the LINQ query in order to evaluate it into a list.
  • You can map it into PivotedMean instances instead of anonymous objects.

For example:

public List<PivotedMean> AssociateResultsWithCollectionDate(List<MeanData> meanData)
{

    var currentMonth = DateTime.Now.Month;
    return meanData
        .GroupBy(i => i.Description)
        .Select(g => new PivotedMean // notice the class name here
        { // this is now a PivotedMean initializer
            Description = g.Key, // You were also missing some caps here
            Month3 = g.Where(c => c.CollPeriod.Month == currentMonth - 3),
            Month2 = g.Where(c => c.CollPeriod.Month == currentMonth - 2),
            Month1 = g.Where(c => c.CollPeriod.Month == currentMonth - 1)
        }).ToList(); // notice the ToList

}

Alternatively, work with IEnumerables instead of Lists when possible. They provide an abstraction over "going through things".

When you code, coding against an interface and not an implementation is considered best practice since you only worry about and depend on the behavior of your elements and not their actual type.

Upvotes: 4

Related Questions