LINQ Unpivot a list

Question

I have a table which spreads weekly values into 52 columns, together with Product and Location columns. E.g.

Product|WK-0|WK-1|WK-2|...|WK-52|Location

I'm trying to transpose this table into row as such :

Product|Location|Week|Value

Based on example, I created following code :

var columns = 53;
var testList =from n in Enumerable.Range(1, columns)
              let c = db.Stock_On_Hand_Files
                        .Where(p => p.Product == "00009E85 " && p.Location == "A_DOMEST")
                        .First()
              select new
              {
                Product = c.Product,
                Location = c.Location,
                Column = n.ToString(),
                SOH = c[n]
               };

But I'm getting following error on the last line

Error CS0021 Cannot apply indexing with [] to an expression of type 'Stock_On_Hand_File'

As requested, schema for Stock On Hand File.

    public partial class Stock_On_Hand_File
{
    public string Product { get; set; }
    public Nullable<double> PastDue { get; set; }
    public Nullable<double> WK_001 { get; set; }
    public Nullable<double> WK_002 { get; set; }
    public Nullable<double> WK_003 { get; set; }
    public Nullable<double> WK_004 { get; set; }
    public Nullable<double> WK_005 { get; set; }
    public Nullable<double> WK_006 { get; set; }
    public Nullable<double> WK_007 { get; set; }
    public Nullable<double> WK_008 { get; set; }
    public Nullable<double> WK_009 { get; set; }
    public Nullable<double> WK_010 { get; set; }
    public Nullable<double> WK_011 { get; set; }
    public Nullable<double> WK_012 { get; set; }
    public Nullable<double> WK_013 { get; set; }
    public Nullable<double> WK_014 { get; set; }
    public Nullable<double> WK_015 { get; set; }
    public Nullable<double> WK_016 { get; set; }
    public Nullable<double> WK_017 { get; set; }
    public Nullable<double> WK_018 { get; set; }
    public Nullable<double> WK_019 { get; set; }
    public Nullable<double> WK_020 { get; set; }
    public Nullable<double> WK_021 { get; set; }
    public Nullable<double> WK_022 { get; set; }
    public Nullable<double> WK_023 { get; set; }
    public Nullable<double> WK_024 { get; set; }
    public Nullable<double> WK_025 { get; set; }
    public Nullable<double> WK_026 { get; set; }
    public Nullable<double> WK_027 { get; set; }
    public Nullable<double> WK_028 { get; set; }
    public Nullable<double> WK_029 { get; set; }
    public Nullable<double> WK_030 { get; set; }
    public Nullable<double> WK_031 { get; set; }
    public Nullable<double> WK_032 { get; set; }
    public Nullable<double> WK_033 { get; set; }
    public Nullable<double> WK_034 { get; set; }
    public Nullable<double> WK_035 { get; set; }
    public Nullable<double> WK_036 { get; set; }
    public Nullable<double> WK_037 { get; set; }
    public Nullable<double> WK_038 { get; set; }
    public Nullable<double> WK_039 { get; set; }
    public Nullable<double> WK_040 { get; set; }
    public Nullable<double> WK_041 { get; set; }
    public Nullable<double> WK_042 { get; set; }
    public Nullable<double> WK_043 { get; set; }
    public Nullable<double> WK_044 { get; set; }
    public Nullable<double> WK_045 { get; set; }
    public Nullable<double> WK_046 { get; set; }
    public Nullable<double> WK_047 { get; set; }
    public Nullable<double> WK_048 { get; set; }
    public Nullable<double> WK_049 { get; set; }
    public Nullable<double> WK_050 { get; set; }
    public Nullable<double> WK_051 { get; set; }
    public Nullable<double> WK_052 { get; set; }
    public string Location { get; set; }
    public Nullable<System.DateTime> UpdateDate { get; set; }
}

That line will generate 53 calls to the DB.. oooooh (the `let` is INSIDE the `from`) — xanatos, Mar 02 '17 at 13:46
And you can't access a field by index... In C# it is nearly impossible if you have distinct properties named `Foo1`, `Foo2`, `Foo3` to access them like `"Foo" + ix` — xanatos, Mar 02 '17 at 13:48
I would make the weeks a list() object or dictionary as follows : Dictionary wkDict; — jdweng, Mar 02 '17 at 14:23

score 3 · Answer 1 · answered Mar 02 '17 at 14:41

Another way:

var item = db.Stock_On_Hand_Files
    .Where(p => p.Product == "00009E85 " && p.Location == "A_DOMEST")
    .First();

var arr = new[] { item.WK_001, item.WK_002, item.WK_003, ... continue for all weeks };
var result = arr.Select((x, i) => new
{
    Product = item.Product,
    Location = item.Location,
    Column = (i+1).ToString(),
    SOH = x
});

Or, if you want to do it for a whole list:

var result = db.Stock_On_Hand_Files
    .Where(p => p.Product == "00009E85 " && p.Location == "A_DOMEST")
    .SelectMany(item => {

        var arr = new[] { item.WK_001, item.WK_002, item.WK_003, ... continue for all weeks };
        return arr.Select((x, i) => new
        {
            Product = item.Product,
            Location = item.Location,
            Column = (i+1).ToString(),
            SOH = x
        });
    });

xanatos · Accepted Answer · 2017-03-02T14:12:49.177

Using a little reflection, a little caching of the generated delegates:

static readonly Func<Stock_On_Hand_File, Nullable<double>>[] Getters = GenerateGetters();

static Func<Stock_On_Hand_File, Nullable<double>>[] GenerateGetters()
{
    var getters = new Func<Stock_On_Hand_File, Nullable<double>>[52];

    for (int i = 0; i < getters.Length; i++)
    {
        var getter = typeof(Stock_On_Hand_File).GetProperty("WK_" + (i + 1).ToString("D3")).GetGetMethod();
        getters[i] = (Func<Stock_On_Hand_File, Nullable<double>>)Delegate.CreateDelegate(typeof(Func<Stock_On_Hand_File, Nullable<double>>), getter);
    }

    return getters;
}

and then

var row = db.Stock_On_Hand_Files.Where(p => p.Product == "00009E85" && p.Location == "A_DOMEST").First();

var result = Enumerable.Range(0, Getters.Length).Select(x => new
{
    row.Product,
    row.Location,
    Week = Getters[x](row),
}).ToArray();

If you want to pivot multiple "base" rows:

var rows = db.Stock_On_Hand_Files.Where(p => p.Product == "00009E85" && p.Location == "A_DOMEST");

var result = from row in rows.AsEnumerable()
             from x in Enumerable.Range(0, Getters.Length)
             select new 
             {
                 row.Product,
                 row.Location,
                 Week = Getters[x](row),
             };

Note the use of AsEnumerable() to force the second part of the query to be executed locally (and not on DB)

Note that you don't need reflection to do this code... You can do it "manually":

static readonly Func<Stock_On_Hand_File, Nullable<double>>[] Getters = new Func<Stock_On_Hand_File, Nullable<double>>[] 
{
                   x => x.WK_001, x => x.WK_002, x => x.WK_003, x => x.WK_004, x => x.WK_005, x => x.WK_006, x => x.WK_007, x => x.WK_008, x => x.WK_009,
    x => x.WK_010, x => x.WK_011, x => x.WK_012, x => x.WK_013, x => x.WK_014, x => x.WK_015, x => x.WK_016, x => x.WK_017, x => x.WK_018, x => x.WK_019,
    x => x.WK_020, x => x.WK_021, x => x.WK_022, x => x.WK_023, x => x.WK_024, x => x.WK_025, x => x.WK_026, x => x.WK_027, x => x.WK_028, x => x.WK_029,
    x => x.WK_030, x => x.WK_031, x => x.WK_032, x => x.WK_033, x => x.WK_034, x => x.WK_035, x => x.WK_036, x => x.WK_037, x => x.WK_038, x => x.WK_039,
    x => x.WK_040, x => x.WK_041, x => x.WK_042, x => x.WK_043, x => x.WK_044, x => x.WK_045, x => x.WK_046, x => x.WK_047, x => x.WK_048, x => x.WK_049,
    x => x.WK_050, x => x.WK_051, x => x.WK_052,
};

Another way: `double?[] GetValues(Stock_On_Hand_File x) { return new[] { x.WK_001, x.WK_002, x.WK_003, ... etc };} ` And then a bit of shuffling around with LINQ (see: `Enumerable.SelectMany()`). Reduces the number of function calls. — Vilx-, Mar 02 '17 at 14:29
@Vilx That is a very simple and minimalistic solution :-) You should post it. — xanatos, Mar 02 '17 at 14:32

score 0 · Answer 3 · answered Mar 02 '17 at 13:59

The only way your code would work is if your class had an indexer and your properties were aware of it. For example:

public partial class Stock_On_Hand_File
{
    private Nullable<double>[] _weeks = new Nullable<double>[52];

    public string Product { get; set; }
    public Nullable<double> PastDue { get; set; }

    public Nullable<double> WK_001 
    {
        get { return _weeks[0]; }
        set { _weeks[0] = value; }
    }

    public Nullable<double> WK_002 
    {
        get { return _weeks[1]; }
        set { _weeks[1] = value; }
    }

    //etc...

    public Nullable<double> this[int index]
    {
        get { return _weeks[index]; }
        set { _weeks[index] = value; }
    }

    //snip
}

Note: This might cause issues if you try to use it with an ORM like Entity Framework...

score 0 · Answer 4 · edited May 23 '17 at 10:29

You are trying to access fields of a class in an index.

So either you

use reflection to get the value at these fields:
(double) c.GetType().GetProperty($"WK_{n:D3}").GetValue(c, null)
Or you use reflection to convert to a DataTable and then you can access them with the indexer. You can see how to do it here: https://stackoverflow.com/a/24131656/1375753

Or you create an indexer in the class itself and then use a switch or reflection to serve the appropriate value:

public double this[int index] 
{
    switch (index)
    {
        case 1: return this.WK_001
        case 2: return this.WK_002
    }

    // Or: return (double) this.GetType().GetProperty($"WK_{index:D3}").GetValue(c, null);
}

LINQ Unpivot a list

4 Answers4