Need help writing nested SQL query in C#

Question

I need some help with building an SQL query. Here's the scenario.

In this only table I have, there are over 22 columns. For now I'm only focusing on 3:

---------------------------------------------------------
|   id      |   Status      |   EditNumber  |
---------------------------------------------------------
|   A0001   |     Approved  |   AG01        |        
|   A0002   |     Approved  |   AG02        |        
|   A0003   |     Approved  |   AG01        |        
|   A0004   |     Approved  |   AG03        |        
|   A0005   |     Other     |   AG01        |        
|   A0006   |     Other     |   AG02        |        
|   A0007   |     Pending   |   AG01        |        
|   A0008   |     Pending   |   AG03        |        
|   A0009   |     Denied    |   AG04        |        
|   A0010   |     Denied    |   AG03        |        
|   A0011   |     Approved  |   AG02        |        
|   A0012   |     Approved  |   AG01        |        
|   A0013   |     Approved  |   AG03        |        
|   A0014   |     Denied    |   AG01        |        
|   A0015   |     Pending   |   AG04        |        
|   A0016   |     Pending   |   AG01        |        
---------------------------------------------------------

This is just an example. Now, what I need is the count of each EditNumber and the count of each status for each EditNumber

This is what I have done.

var subquery = from n in db.Claims
               group n by new { n.id, EditNumber = n.EditNumber.Substring(0, 4) } into g
               select new
               {
                  Key = g.Key,
                  Count = g.Count()
               };


var count = (from c in db.Claims
            join s in subquery on c.ClaimID equals s.Key.ClaimID
            group s by s.Key.EditNumber into g
            join e in db.EditOverrides on g.Key equals e.EditNumber
            orderby g.Count() descending
            select new EOSummaryCount
            {
                EditNumber = g.Key,
                Count = g.Count(),
                Description = e.Description
            }).AsEnumerable();

but this only gives me the count of EditNumber.

and the following:

db.C.Where(w => w.RecordType == type)
    .GroupBy(x => x.Status)
    .Select(s => new StatusCount { Status = s.Key, Count = s.Count() }); 

only gives me the status count. But only gives me in format i.e. in JSON I see the result as

{
    "key":"PENDING",
    "count":93
},{
    "key":"CLOSED",
    "count":128
},{
    "key":"MEDREVIEW",
    "count":218
},{
    "key":"APPROVED",
    "count":3946
},{
    "key":"DENIED",
    "count":746
}

What I ultimately want in JSON format is something like this:

[{
    "editNumber":"AG001",
    "count":2195,
    "status":[
    {
         "key":"INPROCESS",
         "count":3
    },{
         "key":"PENDING", 
         "count":93
    },{
         "key":"CLOSED",
         "count":128
    },{
         "key":"MEDREVIEW",
         "count":218
    },{
         "key":"APPROVED",
         "count":3946
    },{
         "key":"DENIED",
         "count":746
    }]
},{
    "editNumber":"AG002",
    "count":234,
    "status":[
    {
         "key":"INPROCESS",
         "count":3
    },{
         "key":"PENDING",
         "count":93
    },{
         "key":"CLOSED",
         "count":0
    },{
         "key":"MEDREVIEW",
         "count":218
    },{
         "key":"APPROVED",
         "count":104
    },{
         "key":"DENIED",
         "count":30
    }]
}]

How can I get this result? I couldn't figure out how I could join those two together to get a new result with the above JSON. Btw, it's all in WebApi controller that returns HttpResponseMessage.

Answer 1

You can do it as below

from p in Samples
              group p.Status by p.EditNumber into g
              select new { EditNumber = g.Key,  EditCount= g.Count(),
              StatusList =   from x in g.ToList() group x  by x into xc select new { Status = xc.Key, StatusCount= xc.Count() }  }

Answer 2

Without seeing your table structure, you could try this:

var result = db.Claims
    .GroupBy(c => c.EditNumber)
    .Select(g => new {
        editNumber = g.FirstOrDefault().EditNumber,
        count = g.Count(),
        status = g.GroupBy(x => x.Status).Select(sg => new { 
            key = sg.FirstOrDefault().Status,
            count = sg.Count(),
        })
    }).ToList();