Linq Pivot from SQL Query

Question

I have problem with Linq, the code used to be processed in sql via stored procedure, but right now it supposed to be on code via linq, here's my database schema

SQL Fiddle

what I want is , from this data

orderNo	Type	serial
1	BN	BN 1
1	BE	BE 1
2	BN	BN 2
2	BE	BE 2
3	BN	BN 3
3	BE	BE 3

to be like this :

orderNo	be	bn
1	BE 1	BN 1
2	BE 2	BN 3
3	BE 2	BN 3

found one question and solution Source 1 - Stackoverflow , when I tried to my code, I got an issue with SelectMany

here's what I've tried

var results = data_tech.GroupBy(l => l.serial).SelectMany( g => 
                     new 
                     { 
                         Metadata = g.Key, 
                         data = g 
                     });


 var pivoted = new List<PivotedEntity>();

foreach(var item in results)
{
    pivoted.Add( 
        new PivotedEntity
        {
            Order= item.orderNo,
            BE= item.data.Where(x => x.Name == "BE")
                        .FirstOrDefault().value,
            BN= item.data.Where(x => x.Name == "BN")
                         .FirstOrDefault().value,
        });
}

Change it to `GroupBy(l => l.OrderNo)` and add `BE = g.FirstOrDefault(x=> x.Type == "BE" && x.OrderNo == g.Key).Serial` to the anonymous object. — iSR5, Aug 16 '21 at 17:23

score 2 · Accepted Answer · edited Aug 16 '21 at 18:06

You can simply achieve this by changing the group by element serial to OrderNo. Let me give you an example,

        var list = new List<Order>() {
            new Order { orderNo = 1, Type = "BN", Serial = "BN 1" },
            new Order { orderNo = 1, Type = "BE", Serial = "BE 1" },
            new Order { orderNo = 2, Type = "BN", Serial = "BN 2" },
            new Order { orderNo = 2, Type = "BE", Serial = "BE 2" },
            new Order { orderNo = 3, Type = "BN", Serial = "BE 3" } ,
            new Order { orderNo = 3, Type = "BE", Serial = "BN 3" } };

            var results = list.GroupBy(l => l.orderNo).Select(g =>
                new
                {
                    Metadata = g.Key,
                    data = g
                });

            var pivoted = new List<PivotedEntity>();

            foreach (var item in results)
            {
                pivoted.Add(
                    new PivotedEntity
                    {
                        Order = item.Metadata,
                        BE = item.data.Where(x => x.Type == "BE")
                                    .FirstOrDefault().Serial,
                        BN = item.data.Where(x => x.Type == "BN")
                                     .FirstOrDefault().Serial,
                    });
            }

This will give you some output like this image.

Edit: Output PivotedEntity class =>

internal class PivotedEntity
    {
        public int Order { get; set; }
        public string BE { get; set; }
        public string BN { get; set; }
    }

Linq Pivot from SQL Query

1 Answers1