Aritra B Aritra B - 4 years ago 184
C# Question

Lambda Expression for Unpivoting DataTable

I am reading data from an excel sheet in the following format -

enter image description here

and I need to store the data in the following way-

enter image description here

I am trying to do it with the help of Linq lambda expression but I think I'm not getting anywhere with this.

What I tried -

DataTable dataTable= ReadExcel();
var dt = dataTable.AsEnumerable();

var resultSet = dt.Where(x => !String.IsNullOrEmpty(x.Field<String>("Project_Code")))
.GroupBy(x =>
new
{
Month = x.Field<String>("Month"),
ProjectCode = x.Field<String>("Project_Code"),
//change designation columns into row data and then group on it
//Designation =
}
);
//.Select(p =>
// new
// {
// Month= p.d
// }
// );`

Answer Source

I would use ToDictionary with a pre-defined set of designation names:

private static readonly string[] designationNames = {"PA","A","SA","M","SM","CON"};
void Function()
{
    /* ... */
    var resultSet = dt.Where(x => !String.IsNullOrEmpty(x.Field<String>("Project_Code")))
            .Select(x =>
                new
                {
                    Month = x.Field<String>("Month"),
                    ProjectCode = x.Field<String>("Project_Code"),
                    Designations = designationNames.ToDictionary(d => d, d => x.Field<int>(d))
                }
            );
}

This is the normalized version. If you want it flat instead, use:

private static readonly string[] designationNames = {"PA","A","SA","M","SM","CON"};

void Function()
{
    /* ... */
    var resultSet = dt.Where(x => !String.IsNullOrEmpty(x.Field<String>("Project_Code")))
        .Select(x =>
            designationNames.Select(
                d =>
                    new
                    {
                        Month = x.Field<String>("Month"),
                        ProjectCode = x.Field<String>("Project_Code"),
                        Designation = d,
                        Count = x.Field<int>(d)
                    }
            )
        ).SelectMany(x => x).ToList();
}

If the type is not always int then you might want to use x.Field<String>(d) instead and check for validity.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download