CODEWITHSUNDEEP
c#linq
PixelPaul
PixelPaul

Reputation: 2801

Linq query to object with nested list

I have the following LINQ query that creates the following outputs:

var query = from s in db.Sku
            join sc in db.SkuCombo on s.Sku equals sc.Sku
            join s2 in db.Sku on sc.SkuId equals s2.Sku
            where skulist.Contains(s.Sku)
            select new
            {
                Sku = s.Sku,
                SkuQty = s.SkuQty,
                ComboSku = s2.Sku,
                ComboSkuQty = sc.Qty
            };

Sku         SkuQty      ComboSku    ComboSkuQty 
===============================================
ABC-123     1           CCC-111     2 
ABC-123     1           DDD-222     3 
ABC-123     1           EEE-333     1 
ABC-123     1           FFF-444     4 
XYZ-789     3           JJJ-777     5 
XYZ-789     3           KKK-888     1

I would like the output to be mapped to C# class OrderItem, but I'm not sure how to go about this. I'm pretty sure that I need a group by on the Sku?

public class OrderItem
{
    public string Sku { get; set; }
    public int SkuQty { get; set; }
    public List<ComboItem> ComboItems { get; set; }
}

public class ComboItem
{
    public string ComboSku { get; set; }
    public int ComboSkuQty { get; set; }
}

Upvotes: 0

Views: 952

Answers (1)

jdweng
jdweng

Reputation: 34431

Try following :

           var query = (from s in db.Sku
                         join sc in db.SkuCombo on s.Sku equals sc.Sku
                         join s2 in db.Sku on sc.SkuId equals s2.Sku
                         where skulist.Contains(s.Sku)
                         select new
                         {
                             Sku = s.Sku,
                             SkuQty = s.SkuQty,
                             ComboSku = s2.Sku,
                             ComboSkuQty = sc.Qty
                         }).GroupBy(x => x.Sku)
                        .Select(x => new OrderItem()
                        {
                            Sku = x.Key,
                            SkuQty = x.Sum(y => y.SkuQty),
                            ComboItems = x.Select(y => new ComboItem()
                            {
                                ComboSku = y.ComboSku,
                                ComboSkuQty = y.ComboSkuQty
                            }).ToList()
                        }).ToList();

Upvotes: 1

Related Questions