How to query / transform the data into the desired format

Question

I wonder if anyone can help me to transform the below data (from a database) into List.:

Lorryid	productid	qtytype	Qty	iscomment	comment
1	4090	3	2	f
1	4153	3	1	f
1	4153	3	1	f
1	4153	3	1	f
1	4153	3	1	f
1	4153	3	1	f
1	31068	3	2	f
1	31069	3	2	f
1	31173	3	2	f
2	17	0	1.000	f
2	150	3	6.000	f
2	216	3	6.000	f
2	278	2	1.020	f
2	398	2	1.125	f
2	398	2	1.090	f
2	398	2	0	t	MUST BE RIPE
2	431	2	3.000	f
2	436	3	1.000	f
2	446	0	1	f
2	446	2	3.045	f
2	451	3	2.000	f
2	457	3	1.000	f
2	458	3	4.000	f
2	478	3	1.000	f
2	510	2	1.140	f
2	518	3	3.000	f
2	518	3	4.000	f
2	550	2	1.170	f
2	550	3	1.000	f

Into the below object.

public class View
{
  public List<LorryLoading> Report
}

public class LorryLoading
{
  public int LorryId
  public List<Product> Product
}

public class Product
{
  public int ProductId
  public decimal Qty0 sum(Quantity) WHERE QtyType = 0
  public decimal Qty2 sum(Quantity) WHERE QtyType = 2
  public decimal Qty3 sum(Quantity) WHERE QtyType = 3
  public List<string> Comments
}

Is it possible to do with a single projection? Having problems on assigning lists.

Also, any links where I can learn such transformations? The examples I've found so far are rather simple or poorly explained.

Answer 1

You can use Linq's Aggregate to do a custom aggregation. This is slightly more complex as you have a double aggregation going on here.

Note that this will probably only work with Linq-to-Objects.

var report = data
    .GroupBy(row => row.Lorryid)
    .Select(l => new LorryLoading {
        LorryId = l.Key,
        Product = l
            .GroupBy(row => row.productid)
            .Select(p => p.Aggregate(
                new Product {ProductId = p.Key},  // this is the starting object
                (prod, row) => {                  // this aggregates it up
                    if(comment != null)
                        prod.Comments.Add(row.comment);
                    if(QtyType == 0)
                        prod.Qty0 += row.Quantity;
                    else if(QtyType == 2)
                        prod.Qty2 += row.Quantity;
                    else if(QtyType == 3)
                        prod.Qty3 += row.Quantity;
                })
            .ToList()
     }).ToList();

var view = new View {Report = report};