Complex GROUP BY using LINQ

Question

I am familiar with simple GROUP BY operation in LINQ. But I have a complex scenario. I need to convert the CostPageRow records into CostPageSelection domain objects using group by. How can we get the domain objects from the List?

Note: The group by that I wrote above does not give List. I am not sure how to write it. The CostPageSelection has a list inside it.

List<CostPageRow> costPageRows = searchDAL
    .GetAllCostPages(contextObject, searchCriteria);


var orderGroups = costPageRows
     .GroupBy(x => new { 
         x.CostPage, 
         x.Description, 
         x.BillTypeDirect,
         x.BillTypeWarehouse,
         x.OrderType,
         x.Vendor,
         x.VendorID 
     })
     .Select(y => new CostPageParent { 
           CostPage = y.First().CostPage 
     })
     .ToList();

Result from Stored Procedure

public class CostPageRow
{
    //CostPage Parent

    public string CostPage { get; set; }
    public string Description { get; set; }
    public string Vendor { get; set; }
    public string VendorID { get; set; }
    public string BillTypeDirect { get; set; }
    public string BillTypeWarehouse { get; set; }
    public string OrderType { get; set; }

    //Item Chilld
    public string ItemID { get; set; }
    public string ItemDescription { get; set; }
    public string BrandCode { get; set; }
    public string PackSize { get; set; }
}

Domain Model

public class CostPageSelection
{
    public CostPageParent CostPageParent { get; set; }
    public List<ItemChild> ChildItems { get; set; }
}


//CostPageParent
public class CostPageParent
{
    public int? SelectedCostPageID { get; set; }
    public string CostPage { get; set; }
    public string Description { get; set; }
    public string Vendor { get; set; }
    public string VendorID { get; set; }
    public string BillTypeDirect { get; set; }
    public string BillTypeWarehouse { get; set; }
    public string OrderType { get; set; }
}

//ItemChild
public class ItemChild
{
    public int? SelectedItemID { get; set; }
    public string ItemID { get; set; }
    public string ItemDescription { get; set; }
    public string BrandCode { get; set; }
    public string PackSize { get; set; }
}

Answer 1

From your question, it appears that your database contains a flattened hierarchy of your parent-child relationships, and it would be much better if the database schema was normalized to avoid data duplication: in other words, each CostPageRow row should contain a reference to a different table containing the related CostPageParent instance (but I presume you already have a running database and this is not an option).

To solve your current problem as it is, you need to extract the properties which define a single group out of each CostPageRow instance (these properties will form a new CostPageParent instance), then create groups using these CostPageParent instances as unique keys, and finally project the groups to new instances of CostPageSelection (each with a unique CostPageParent key).

You need to modify your code to use the IGrouping<T>.Key property to get the group key, after you create it:

var groups = costPageRows
    .GroupBy(x => new CostPageParent()
        {
            CostPage = x.CostPage,
            Description = x.Description,
            BillTypeDirect =  x.BillTypeDirect,
            BillTypeWarehouse = x.BillTypeWarehouse,
            OrderType = x.OrderType,
            Vendor = x.Vendor
        }, 
        new CostPageParentEqualityComparer())
    .Select(y => new CostPageSelection
        {
            CostPageParent = y.Key,
            ChildItems = y.Select(i => 
                new ItemChild()
                { 
                    BrandCode = i.BrandCode,
                    ItemDescription = i.ItemDescription,
                    ItemID = i.ItemID,
                    PackSize = i.PackSize
                })
                .ToList()
        })
    .ToList();

Note that you need to specify the IEqualityComparer<CostPageParent> implementation to make the grouping work property:

class CostPageParentEqualityComparer : IEqualityComparer<CostPageParent>
{
    public bool Equals(CostPageParent x, CostPageParent y)
    {
        if (x == null)
            return y == null;

        if (object.ReferenceEquals(x, y))
            return true;

        return 
            x.BillTypeDirect == y.BillTypeDirect &&
            x.BillTypeWarehouse == y.BillTypeWarehouse &&
            ...
    }

    public int GetHashCode(CostPageParent obj)
    {
        var x = 31;
        x = x * 17 + obj.BillTypeDirect.GetHashCode();
        x = x * 17 + obj.BillTypeWarehouse.GetHashCode();
        ...
        return x;
    }
}

Answer 2

I think you are looking for something like this:

var orderGroups = costPageRows
 .GroupBy(x => new { 
     x.CostPage, 
     x.Description, 
     x.BillTypeDirect,
     x.BillTypeWarehouse,
     x.OrderType,
     x.Vendor,
     x.VendorID 
 })
 // The following statement is basically a let statement from query expression
 .Select(y => new {
      y, 
      first = y.First()
 })
 // What happens next is projection into the CostPageSelection type using z.first
 // and then all the grouped items in z are projected into the ItemChild type
 .Select(z => new CostPageSelection {
      new CostPageParent { 
          z.first.CostPage, 
          // other props 
          ChildItems = z.Select(i => new ItemChild {
              i.ItemID,
              // other props 
          }).ToList()
 })
 .ToList();