在实体框架c#中添加DTO创建的对象列表

Question

我有以下代码在我的c#控制器，

[HttpGet("antifungal/{name}")]
public List<DrugInteractionDTO> test(string name)
{
var DI = (from D1 in _context.DrugInteractions
join D2 in _context.DrugInteractionReferences
on D1.ID equals D2.DrugInteractionId into joined
from D3 in joined.DefaultIfEmpty()
where D1.AntifungalAgent.Name.ToLower().Contains(name.ToLower())
select new DrugInteractionDTO
{
Severity = D1.Severity,
SeverityAsString = D1.Severity.ToString(),
ProDetailedInformation = D1.ProDetailedInformation,
BasicDetailedInformation = D1.BasicDetailedInformation,
Antifungal = D1.AntifungalAgent.Name,
InteractingDrug = D1.InteractingDrug.GenericName,
ID = D1.ID,
Count = 2
//DrugInteractionReferences
}).ToList();
return DI.OrderBy(x => x.InteractingDrug).ToList();
}

我的DrugInteractionDTO模型有一个字段List<DrugInteractionReferences>，我想添加到。

我的两个表是interactions和references，每个相互作用可以有许多引用。

我习惯使用Java (Spring)，我不知道如何返回属于DTO中每个交互的引用。我目前正在帮助的应用程序是非常古老的，有很多很多的问题。

是否可以将引用列表添加到在此代码中创建的每个interactionDTO ?

Answer 1

这被称为急切加载查询，详细实体不应该通过join检索，而是通过子查询表达式:

var DI = (from D1 in _context.DrugInteractions
where D1.AntifungalAgent.Name.ToLower().Contains(name.ToLower())
select new DrugInteractionDTO
{
Severity = D1.Severity,
SeverityAsString = D1.Severity.ToString(),
ProDetailedInformation = D1.ProDetailedInformation,
BasicDetailedInformation = D1.BasicDetailedInformation,
Antifungal = D1.AntifungalAgent.Name,
InteractingDrug = D1.InteractingDrug.GenericName,
ID = D1.ID,
Count = 2,
DrugInteractionReferences = _context.DrugInteractionReferences
.Where(D2 => D2.DrugInteractionId == D1.ID)
.ToList()
}).ToList();

如果遇到性能问题，在最后一个.ToList()之前添加.AsSplitQuery()