基于Angular13+ASP.NET Core6+SQL Server统计各语言用户数问题

1. SQL Server 层面实现（最优方案） #

直接通过一次关联查询统计每种语言的订阅用户数，彻底避免多次查询的性能问题：

SELECT 
    l.Id,
    l.Name,
    l.Description,
    COUNT(u.Id) AS SubscriberCount
FROM Languages l
LEFT JOIN Users u ON l.Id = u.LanguageId
GROUP BY l.Id, l.Name, l.Description
ORDER BY l.Name

说明：用LEFT JOIN确保没有订阅用户的语言也会被返回（此时SubscriberCount为0）；GROUP BY按语言唯一标识分组统计。

2. ASP.NET Core 6 后端处理 #

步骤1：创建数据传输对象（DTO） #

public class LanguageWithSubscriberDto
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public int SubscriberCount { get; set; }
}

步骤2：用EF Core实现查询逻辑 #

public async Task<List<LanguageWithSubscriberDto>> GetLanguagesWithSubscriberCounts()
{
    return await _context.Languages
        .GroupJoin(
            _context.Users,
            lang => lang.Id,
            user => user.LanguageId,
            (lang, users) => new LanguageWithSubscriberDto
            {
                Id = lang.Id,
                Name = lang.Name,
                Description = lang.Description,
                SubscriberCount = users.Count()
            })
        .OrderBy(lang => lang.Name)
        .ToListAsync();
}

步骤3：暴露API接口 #

[HttpGet("with-subscribers")]
public async Task<IActionResult> GetLanguagesWithSubscribers()
{
    var result = await _languageService.GetLanguagesWithSubscriberCounts();
    return Ok(result);
}

后端一次性返回所有语言及对应订阅数，解决二次循环查询无效的问题。

3. Angular 13 前端渲染 #

步骤1：定义前端接口 #

export interface LanguageWithSubscriber {
  id: number;
  name: string;
  description: string;
  subscriberCount: number;
}

步骤2：组件中获取数据 #

import { Component, OnInit } from '@angular/core';
import { HttpClient } from '@angular/common/http';
import { LanguageWithSubscriber } from './models/language.model';

@Component({
  selector: 'app-language-list',
  templateUrl: './language-list.component.html'
})
export class LanguageListComponent implements OnInit {
  languages: LanguageWithSubscriber[] = [];

  constructor(private http: HttpClient) {}

  ngOnInit(): void {
    this.http.get<LanguageWithSubscriber[]>('/api/languages/with-subscribers')
      .subscribe(data => {
        this.languages = data;
      });
  }
}

步骤3：模板渲染（无无限循环） #

<ul>
  <li *ngFor="let lang of languages">
    {{ lang.name }} - 订阅人数: {{ lang.subscriberCount }}
    <p>{{ lang.description }}</p>
  </li>
</ul>

直接用预加载的列表渲染，避免在*ngFor中调用服务，从根源解决无限循环崩溃问题。

内容的提问来源于stack exchange，提问作者Mohamed Sami Khiari