Complex GROUP BY using LINQ

c#, linq

Solution

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;
    }
}

Problem

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; } } ```

Original source