如何用LINQ在ICollection中关联查询Pokemon与Category数据？

我刚开始使用ASP.NET Core Web API，遇到查询关联数据的问题。我需要在接口中同时返回Pokemon实体及其对应的Category信息，但现有查询无法得到预期结果。

定义的实体模型 #

public class Pokemon
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime BirthDate { get; set; }

    public ICollection<Review> Reviews { get; set; }
    public ICollection<PokemonOwner> PokemonOwners { get; set; }
    public ICollection<PokemonCategory> PokemonCategories { get; set; }
}

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }

    public ICollection<PokemonCategory> PokemonCategories { get; set; }
}

public class PokemonCategory
{
    public int PokemonId { get; set; }
    public int CategoryId { get; set; }

    public Pokemon Pokemon { get; set; }
    public Category Category { get; set; }
}

现有查询代码及结果 #

我尝试了以下查询，但返回的是Category而非Pokemon及其分类：

public List<Category> GetPokemonAndCategory(int pokemonid, int categoryid)
{
     return _context.Categories
                    .Include(a => a.PokemonCategories)
                    .Where(c => c.Id == categoryid).ToList();
}

返回结果：

[
  {
    "id": 2,
    "name": "Water",
    "pokemonCategories": [
      {
        "pokemonId": 2,
        "categoryId": 2,
        "pokemon": null,
        "category": null
      }
    ]
  }
]

编辑补充 #

参考建议修改了DTO，但返回结果包含多余字段（如reviews、pokemonOwners以及Category中的循环引用字段）：

public class PokemonCategoryDto
{
    public Pokemon Pokemon { get; set; }
    // public Category Category { get; set; }
}

当前返回结果：

{
  "pokemon": {
    "id": 2,
    "name": "Squirtle",
    "birthDate": "1903-01-01T00:00:00",
    "reviews": null,
    "pokemonOwners": null,
    "pokemonCategories": [
      {
        "pokemonId": 2,
        "categoryId": 2,
        "pokemon": null,
        "category": {
          "id": 2,
          "name": "Water",
          "pokemonCategories": [
            null
          ]
        }
      }
    ]
  }
}

我需要得到如下格式的结果：

{
  "pokemon": {
    "id": 2,
    "name": "Squirtle",
    "birthDate": "1903-01-01T00:00:00",
    "pokemonCategories": [
      {
        "pokemonId": 2,
        "categoryId": 2,
        "category": {
          "id": 2,
          "name": "Water"
        }
      }
    ]
  }
}

解决方案 #

1. 调整查询逻辑，从Pokemon出发加载关联数据

从Pokemon表开始查询，通过Include和ThenInclude加载关联的PokemonCategories以及对应的Category，同时过滤指定的pokemonid和categoryid：

public async Task<PokemonCategoryDto> GetPokemonAndCategory(int pokemonid, int categoryid)
{
    var pokemon = await _context.Pokemons
        .Include(p => p.PokemonCategories)
            .ThenInclude(pc => pc.Category)
        .FirstOrDefaultAsync(p => p.Id == pokemonid);

    // 过滤指定分类的关联数据
    if (pokemon != null)
    {
        pokemon.PokemonCategories = pokemon.PokemonCategories
            .Where(pc => pc.CategoryId == categoryid)
            .ToList();
    }

    // 映射到DTO
    return new PokemonCategoryDto
    {
        Pokemon = new PokemonDto
        {
            Id = pokemon.Id,
            Name = pokemon.Name,
            BirthDate = pokemon.BirthDate,
            PokemonCategories = pokemon.PokemonCategories.Select(pc => new PokemonCategoryDtoItem
            {
                PokemonId = pc.PokemonId,
                CategoryId = pc.CategoryId,
                Category = new CategoryDto
                {
                    Id = pc.Category.Id,
                    Name = pc.Category.Name
                }
            }).ToList()
        }
    };
}

2. 定义分层DTO，只保留需要的字段

避免直接返回实体类（会包含多余关联和循环引用），为每个层级定义DTO：

// 最外层DTO
public class PokemonCategoryDto
{
    public PokemonDto Pokemon { get; set; }
}

// Pokemon的DTO
public class PokemonDto
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime BirthDate { get; set; }
    public List<PokemonCategoryDtoItem> PokemonCategories { get; set; }
}

// PokemonCategory的DTO
public class PokemonCategoryDtoItem
{
    public int PokemonId { get; set; }
    public int CategoryId { get; set; }
    public CategoryDto Category { get; set; }
}

// Category的DTO
public class CategoryDto
{
    public int Id { get; set; }
    public string Name { get; set; }
}

3. 解决循环引用问题（可选）

如果使用Newtonsoft.Json，可在Program.cs中配置忽略循环引用：

builder.Services.AddControllers()
    .AddNewtonsoftJson(options =>
    {
        options.SerializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;
    });

如果使用System.Text.Json：

builder.Services.AddControllers()
    .AddJsonOptions(options =>
    {
        options.JsonSerializerOptions.ReferenceHandler = ReferenceHandler.IgnoreCycles;
    });

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