Lock Lock - 3 years ago 38
C# Question

Way to create composite objects from a single (flat) database query

I am getting product data from our ERP through SQL queries whereby the returned data is very flat- at the Size level. A product has 3 levels:


  • Style

  • Colours

  • Sizes



A style has many colours and a colour has many sizes.

I have created the following models:

public class Ap21Style
{
public int StyleIdx;
public string StyleCode;
public IList<Ap21Clr> Clrs { get; set; } = new List<Ap21Clr>();
}

public class Ap21Clr
{
public int ClrIdx { get; set; }
public string ColourCode { get; set; }
public string ColourName { get; set; }
public string ColourTypeCode { get; set; }
public string ColourTypeName { get; set; }
public IList<Ap21Sku> Skus { get; set; } = new List<Ap21Sku>();
}

public class Ap21Sku
{
public int SkuIdx { get; set; }
public string SizeCode { get; set; }
public int SizeSequence { get; set; }
public string Barcode { get; set; }
}


My
ProductResult
looks like this:

public int StyleIdx { get; set; }
public int ClrIdx { get; set; }
public int SkuIdx { get; set; }
public string StyleCode { get; set; }
public string ColourCode { get; set; }
public string ColourName { get; set; }
public string SizeCode { get; set; }
public int SizeSequence { get; set; }
public string ColourTypeCode { get; set; }
public string ColourTypeName { get; set; }
public string Barcode { get; set; }
public string WebData { get; set; }


What would be an effective way to loop over the results and create the
Ap21Style
models whereby they are a composite object with
Ap21Clr
's, then at the row level, the Colours have
Ap21Sku
's?

Answer Source

Assuming something like this

List<ProductResult> products = GetPropducts();

Composing the styles would involve grouping the data by the composite keys

List<Ap21Style> results = products
    .GroupBy(p => new { p.StyleIdx, p.StyleCode })
    .Select(g => new Ap21Style {
        StyleIdx = g.Key.StyleIdx,
        StyleCode = g.Key.StyleCode,
        Clrs = g.GroupBy(s => new {
            s.ClrIdx,
            s.ColourCode,
            s.ColourName,
            s.ColourTypeCode,
            s.ColourTypeName
        }).Select(g1 => new Ap21Clr {
            ClrIdx = g1.Key.ClrIdx,
            ColourCode = g1.Key.ColourCode,
            ColourName = g1.Key.ColourName,
            ColourTypeCode = g1.Key.ColourTypeCode,
            ColourTypeName = g1.Key.ColourTypeName,
            Skus = g1.Select(s => new Ap21Sku {
                Barcode = s.Barcode,
                SizeCode = s.SizeCode,
                SizeSequence = s.SizeSequence,
                SkuIdx = s.SkuIdx
            }).ToList()
        }).ToList()
    }).ToList();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download