Merging Datatable rows' value into 1 row using LINQ-CodePudding

I have a C# datatable with below structure :

ProductID   PartNo   Grade1   Grade2   Grade3
Product1       P1      A       N/A      Z  
Product1       P2      B        E       Y 
Product1       P3      C        F       X 
Product2       P1      D       N/A      V   
Product2       P3     N/A       G       W

I want to merge the datarows' value into 1 row by grouping product ID and Part No based on order of P1,P2,P3 (fixed list).The end format is P1_P2_P3 So the end result would be :

ProductID    PartNo        Grade1        Grade2     Grade3
Product1    P1_P2_P3        A_B_C        N/A_E_F     Z_Y_X  
Product2    P1_P2_P3      D_N/A_N/A     N/A_N/A_G   V_N/A_W

Instead on foreach loop, is there any better way to achieve the end output thru C# LINQ ?

CodePudding user response：

I think you can group_by PID and then select new object, something like this:

data.
    GroupBy(p => p.Id).
    Select(group => return new {
        Id: group.Key,
        Part_No: group.Value.Sum(group_row.Part_No),
        Grade1: group.Value.Sum(group_row.Grade1),
        etc...
    });

I'm writing this from top of my head, so dont copypasta, but this approach might work...

CodePudding user response：

Please try below LINQ expression,

Prodcuts.GroupBy(product=>product.ProductId).Select(productGroup=>{
            return new { ProductId = productGroup.Key, PartNo = string.Join("_", productGroup.Select(p => p.PartNo)), Grade1 = string.Join("_", productGroup.Select(p => p.Grade1)), Grade2 = string.Join("_", productGroup.Select(p => p.Grade2)), Grade3 = string.Join("_", productGroup.Select(p => p.Grade3)) };
        });