c# - 需要帮助在 C# 中编写嵌套 SQL 查询

Question

我需要一些帮助来构建 SQL 查询。这是场景。

在我仅有的这张表中，有超过 22 列。现在我只关注 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        |        
---------------------------------------------------------

这只是一个例子。现在，我需要的是每个 EditNumber 的计数和每个 EditNumber 的每个状态的计数

这就是我所做的。

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

但这只给我 EditNumber 的计数。

以及以下内容:

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

只给我状态计数。但只给我格式，即在 JSON 中我看到的结果是

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

我最终想要的 JSON 格式是这样的:

[{
    "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
    }]
}]

我怎样才能得到这个结果？我无法弄清楚如何将这两者结合在一起以获得上述 JSON 的新结果。顺便说一句，这一切都在 WebApi Controller 中返回 HttpResponseMessage。

Answer 1

没有看到你的表结构，你可以试试这个:

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