How to do double grouping using LINQ

Question

In the data below i want to find for each unique parent how many unique orders are there

    [Fact]
    public void Test()
    {
        var data = new Order[]
        {
            new Order(){ ParentID =1,ParentName ="Parent 1", OrderID = 11 },
            new Order(){ ParentID =1,ParentName ="Parent 1", OrderID = 11 },
            new Order(){ ParentID =1,ParentName ="Parent 1", OrderID = 11 },

            new Order(){ ParentID =1,ParentName ="Parent 1", OrderID = 22 },
            new Order(){ ParentID =1,ParentName ="Parent 1", OrderID = 22 },
            new Order(){ ParentID =1,ParentName ="Parent 1", OrderID = 22 },

            new Order(){ ParentID =2,ParentName ="Parent 2", OrderID = 33 },
            new Order(){ ParentID =2,ParentName ="Parent 2", OrderID = 33 },
            new Order(){ ParentID =2,ParentName ="Parent 2", OrderID = 33 },

            new Order(){ ParentID =2,ParentName ="Parent 2", OrderID = 44 },
            new Order(){ ParentID =2,ParentName ="Parent 2", OrderID = 44 },

            new Order(){ ParentID =2,ParentName ="Parent 2", OrderID = 55 },
            new Order(){ ParentID =2,ParentName ="Parent 2", OrderID = 55 },
        };


         var queryResult = data
            .GroupBy(x => x.OrderID)
            .Select(x => new
            {
                x.First().ParentID,
                x.First().ParentName,
            })
            .ToList();

        var result = queryResult.GroupBy(x => new { x.ParentID, x.ParentName })
            .Select(x => new Result()
            {
                ParentID = x.Key.ParentID,
                ParentName = x.Key.ParentName,
                Count = x.Count()
            })
            .ToList();

        Assert.Equal(2, result.Count);

        var firstParent = result.Where(x => x.ParentID == 1).SingleOrDefault();
        Assert.Equal(2, firstParent.Count);

        var secondParent = result.Where(x => x.ParentID == 2).SingleOrDefault();
        Assert.Equal(3, secondParent.Count);

    }

The code above is working. But i wanted to check if there is a better way (optimized) to do this. The data may have 1000+ records

dotnetfiddle demo

user_390278 · Answer

i want to find for each unique parent how many unique orders are there

Simple, take all orders, group them by their parent (however you identify them), then count the distinct orders from within those groups (those with unique OrderId fields).

var result =
    (from o in data
    group o.OrderId by new { o.ParentId, o.ParentName } into g
    select new Result
    {
        ParentId = g.Key.ParentId,
        ParentName = g.Key.ParentName,
        Count = g.Distinct().Count(),
    })
    .ToList();

How to do double grouping using LINQ

1 Answers