如何在C# EF查询中为DrugInteractionDTO添加关联References列表？

针对你的需求，完全可以在Entity Framework的查询中为DTO的关联References列表赋值，下面提供两种常用实现方式：

方法一：使用导航属性（推荐） #

如果你的DrugInteraction实体类已经配置了一对多的导航属性（对应数据库的关联关系，类似Spring Data JPA的@OneToMany），比如：

public class DrugInteraction
{
    // 其他已有属性...
    public ICollection<DrugInteractionReference> DrugInteractionReferences { get; set; } = new List<DrugInteractionReference>();
}

可以直接通过Include方法加载关联数据，再投影到DTO时直接赋值：

[HttpGet("antifungal/{name}")]
public List<DrugInteractionDTO> GetDrugInteractionsByAntifungal(string name)
{
    var lowerName = name.ToLower();
    var interactions = _context.DrugInteractions
        .Include(di => di.DrugInteractionReferences) // 加载关联的References数据
        .Where(di => di.AntifungalAgent.Name.ToLower().Contains(lowerName))
        .Select(di => new DrugInteractionDTO
        {
            Severity = di.Severity,
            SeverityAsString = di.Severity.ToString(),
            ProDetailedInformation = di.ProDetailedInformation,
            BasicDetailedInformation = di.BasicDetailedInformation,
            Antifungal = di.AntifungalAgent.Name,
            InteractingDrug = di.InteractingDrug.GenericName,
            ID = di.ID,
            Count = 2,
            DrugInteractionReferences = di.DrugInteractionReferences.ToList() // 赋值关联列表
        })
        .OrderBy(dto => dto.InteractingDrug)
        .ToList();

    return interactions;
}

这种方式更符合Entity Framework的设计习惯，EF会自动生成高效的关联查询，且不会产生重复的DrugInteraction记录。

方法二：手动分组查询（无导航属性时使用） #

如果未配置导航属性，可通过分组查询将同一DrugInteraction对应的DrugInteractionReference归为一组，再赋值给DTO：

[HttpGet("antifungal/{name}")]
public List<DrugInteractionDTO> GetDrugInteractionsByAntifungal(string name)
{
    var lowerName = name.ToLower();
    var query = from di in _context.DrugInteractions
                join dir in _context.DrugInteractionReferences
                    on di.ID equals dir.DrugInteractionId into referencesGroup
                where di.AntifungalAgent.Name.ToLower().Contains(lowerName)
                select new 
                {
                    DrugInteraction = di,
                    References = referencesGroup.ToList()
                };

    var dtos = query.Select(item => new DrugInteractionDTO
    {
        Severity = item.DrugInteraction.Severity,
        SeverityAsString = item.DrugInteraction.Severity.ToString(),
        ProDetailedInformation = item.DrugInteraction.ProDetailedInformation,
        BasicDetailedInformation = item.DrugInteraction.BasicDetailedInformation,
        Antifungal = item.DrugInteraction.AntifungalAgent.Name,
        InteractingDrug = item.DrugInteraction.InteractingDrug.GenericName,
        ID = item.DrugInteraction.ID,
        Count = 2,
        DrugInteractionReferences = item.References
    })
    .OrderBy(dto => dto.InteractingDrug)
    .ToList();

    return dtos;
}

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