LInQ query for creating PIVOT table

Question

I have three tables table1, table2 and table3

Table1

Id Data

1 Data1

2 Data2

3 Data3

Table2

Id Meta data

1 Meta data1

2 Meta data2

“ "

“ "

“ "

Table 3

Id Data ID Meta Data ID Value

1 Data1 Metadata1 Value1

2 Data1 Metadata2 Value2

3 Data2 Metadata1 Value3

4 Data2 Metadata2 Value4

I want to create a pivot table by joining these tables using LINQ queries

My result table should look like this

Data Metadata 1 Metadata2 ‘””” and so on……

Data1 Value1 Value2
Data2 Value3 Value4

What would be the appropriate linq query which may be solution for me to achieve the result?

Answer 1

Can't you just do this? No fancy linq is necessary

var dict = new Dictionary<object, PivotedValue>();

foreach(var t in table3){
    if(!dict.ContainsKey(t.DataID)) dict.Add(t.DataID, new PivotedValue{DataID = t.DataID});
    dict[t.DataID].PivotedFields.Add(t.MetaDataID, t.Value);
}


class PivotedValue{

    object DataID {get;set;}
    Dictionary<object, object> PivotedFields {get;set;}

}