LINQ query to place data in a nested object

Question

I am working on writing a C # LINQ query to retrieve data from an SQL table below

Table 1

Id Status SubStatus
1  Review Pending 
2  Review Complete
3  Review Approved
4  Review Denied    
5  Info   Processing
6  Info   Pending
7  Info   Requested
8  Info   Received
9  Approval Approved
10 Review Approved

From the above table, I would like to put the data in an object as shown below

public class LastStatusDto
    {
        public string StatusName { get; set; }
        public int StatusId { get; set; }
        public int Count { get; set; }
        public IEnumerable<LastStatusChildDataDto> drillDownData { get; set; }
    }

public class LastStatusChildDataDto
{
    public string SubStatusName { get; set; }      
    public int Count { get; set; }
}

and the selection result (for the status "Browse") looks below

            {
              "statusName": "Review",
              "statusId": 0,
              "count": 5,
              "drillDownData": 
              [{
               "subStatusName":"Approved",
               "count":2 
              },
              {
               "subStatusName":"Complete",
               "count":1 
              },
             {
              "subStatusName":"Pending",
              "count":1 
              },
              {
               "subStatusName":"Denied",
               "count":1 
              },
            ]
        }

What is a good way to write a LINQ query for the above scenario. I tried to group status, but could not figure out a way to fit substatusesinto a nested object, as shown below

var res = from status in context.Table1
          group status by status.Status into s

          select new LastStatusDto
          {
               Count = s.Count(),
               StatusName = s.Key,
               drilldownData = {/* ? */}                          
          };

Answer 1

This should do it:

 var res = table1.GroupBy(t => t.Status)
            .Select(g => new LastStatusDto()
            {
                StatusId = 0,
                StatusName = g.Key,
                Count = g.Count(),
                drillDownData = g.GroupBy(s => s.SubStatus)
                    .Select(st => new LastStatusChildDataDto()
                    {
                        SubStatusName = st.Key,
                        Count = st.Count()
                    })
            });

As I mentioned above, it StatusIddoes not take place as a property LastStatusDto, because it is a collection, so I set it 0only to match your structure.