CODEWITHSUNDEEP
c#linq
DoIt
DoIt

Reputation: 3438

LINQ Query to put data in a nested object

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

Table1

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 like 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 sample result(for Status "Review") looks like below

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

Whats a good way to write a LINQ query for the above scenario. I tried grouping by status but not able to figured a way to put substatuses in the nested object like 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 = {/* ? */}                          
          };

Upvotes: 4

Views: 1228

Answers (1)

JuanR
JuanR

Reputation: 7783

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 in a comment above, StatusId has no place as a property of LastStatusDto because it's an aggregate so I set it 0 just to match your structure.

Upvotes: 4

Related Questions