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.

In linq, but in order to learn exactly how it's being done SQL would be great too if it's not too much of a trouble. — user1828605
– user1828605, Commented Oct 13, 2015 at 23:06

Frank Fajardo · Accepted Answer · 2015-10-13 22:22:22Z

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();

answered Oct 13, 2015 at 22:22

Frank Fajardo

7,4291 gold badge31 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1828605 Over a year ago

This is exactly what I was looking for. Thank you all for helping me out.

Hakunamatata · Accepted Answer · 2015-10-13 22:44:30Z

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() }  }

answered Oct 13, 2015 at 22:44

Hakunamatata

1,27313 silver badges19 bronze badges

1 Comment

User2012384 Over a year ago

What program is this...??

Collectives™ on Stack Overflow

Need help writing nested SQL query in C#

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related