How do I pivot dynamically in Linq using this example

The problem below is in the code, i.e. the bit that says DY1 =, DY2 =, etc. I don't know how many there will be so I need to do it dynamically.

Here is my textfile :

Product, Origin Year, Development Year, Incremental Value
Comp, 1992, 1992, 110.0
Comp, 1992, 1993, 170.0
Comp, 1993, 1993, 200.0
Non-Comp, 1990, 1990, 45.2
Non-Comp, 1990, 1991, 64.8
Non-Comp, 1990, 1993, 37.0
Non-Comp, 1991, 1991, 50.0
Non-Comp, 1991, 1992, 75.0
Non-Comp, 1991, 1993, 25.0
Non-Comp, 1992, 1992, 55.0
Non-Comp, 1992, 1993, 85.0
Non-Comp, 1993, 1993, 100.0

Here is my code (pasted from LinqPad) :

void Main()
{
  using (var reader = new StreamReader("C:\\temp\\ExampleData.txt"))
  {
    var products =
        (from line in reader.Lines()
        where !line.StartsWith("Product")
        let parts = line.Split(',')
        select new {Product=parts[0], Origin_Year=parts[1], Development_Year=parts[2], Incremental_Value=parts[3], DY=int.Parse(parts[2]) - int.Parse(parts[1]) + 1 }).ToList();
    products.Dump();

    var grps = from p in products
    group p by new {p.Product, p.Origin_Year}
    into g
    select (new { g.Key.Product, g.Key.Origin_Year, 

    DY1 = g.Where(c => c.DY == 1).Select (c => c.Incremental_Value).FirstOrDefault(),
    DY2 = g.Where(c => c.DY == 2).Select (c => c.Incremental_Value).FirstOrDefault(),
    DY3 = g.Where(c => c.DY == 3).Select (c => c.Incremental_Value).FirstOrDefault(),
    DY4 = g.Where(c => c.DY == 4).Select (c => c.Incremen开发者_Go百科tal_Value).FirstOrDefault()    
    });

    grps.Dump();
  }
}

}// Temporary hack to enable extension methods

/// <summary>
/// Operators for LINQ to Text Files
/// </summary>
public static class Extensions
{
  public static IEnumerable<String> Lines(this TextReader source)
  {
    String line;

    if (source == null)
      throw new ArgumentNullException("source");

    while ((line = source.ReadLine()) != null)
      yield return line;
  }

Here is my output :

Product   Origin_Year  DY1    DY2    DY3   DY4 
Comp      1992         110.0  170.0  null  null  
Comp      1993         200.0  null   null  null  
Non-Comp  1990         45.2   64.8   null  37.0  
Non-Comp  1991         50.0   75.0   25.0  null  
Non-Comp  1992         55.0   85.0   null  null  
Non-Comp  1993         100.0  null   null  null 

---

The best suggestion I can offer is to return an array of values for each possible DY.

Start by modifying Incremental_Value field in the products query to be a double (ie double.Parse(parts[3])).

Now create an enumerable for the maximum number of DYs:

    var dys = Enumerable.Range(1, products.Max(p => p.DY));

Then the grps query looks like this:

    var grps =
        from p in products
        group p by new
        {
            p.Product,
            p.Origin_Year,
        } into g
        let lookup = g.ToLookup(x => x.DY, x => x.Incremental_Value)
        select new
        {
            g.Key.Product,
            g.Key.Origin_Year, 
            DYs = dys.Select(x => lookup[x].Sum()).ToArray(),
        };

Does that work for you?