如何在ASP.NET Web API中统计数据库相同值的数量？

1. 定义数据实体与统计DTO #

假设你的数据库对应实体类（以EF Core为例）如下：

public class UserBook
{
    public string UserName { get; set; }
    public string BookType { get; set; }
    // 其他字段比如Id、BookName等按需保留
}

为了返回结构清晰的统计结果，创建DTO类承载数据：

public class UserBookTypeStats
{
    public string UserName { get; set; }
    public Dictionary<string, int> BookTypeCounts { get; set; }
}

2. 编写分组统计的API接口 #

在控制器中通过Linq查询实现分组统计逻辑：

[ApiController]
[Route("api/[controller]")]
public class BookStatsController : ControllerBase
{
    private readonly YourDbContext _dbContext;

    public BookStatsController(YourDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    [HttpGet("user-book-type-stats")]
    public async Task<ActionResult<List<UserBookTypeStats>>> GetUserBookTypeStats()
    {
        var stats = await _dbContext.UserBooks
            .GroupBy(book => book.UserName)
            .Select(group => new UserBookTypeStats
            {
                UserName = group.Key,
                BookTypeCounts = group.GroupBy(book => book.BookType)
                                      .ToDictionary(typeGroup => typeGroup.Key, typeGroup => typeGroup.Count())
            })
            .ToListAsync();

        return Ok(stats);
    }
}

3. 返回结果示例 #

接口返回的JSON格式完全匹配你的需求：

[
    {
        "userName": "Alex",
        "bookTypeCounts": {
            "stories": 3,
            "culture": 2
        }
    },
    {
        "userName": "Bob",
        "bookTypeCounts": {
            "tech": 5,
            "stories": 1
        }
    }
]

优化提示 #

• 若数据量较大，建议给UserName和BookType添加联合索引，提升分组查询性能
• 需要过滤特定数据时，可在GroupBy前添加Where条件，例如：.Where(book => !string.IsNullOrEmpty(book.UserName))

内容的提问来源于stack exchange，提问作者Kareem