我希望你能帮助我翻译关于Ef Core和Linq的内容。假设我正在进行一个大请求,以获取有关产品、公司等所有支持票证的信息。这很简单,只需连接各种事物:
select *
from Tickets T
left join Products P on T.ProductId = P.Id
left join ProductVersions PV on T.ProductVersionId = PV.Id
left join TicketTypes TT on T.TicketTypeId = TT.Id
left join TicketPriorities TP on T.TicketPriorityId = TP.Id
left join TicketStates TS on T.TicketStateId = TS.Id
left join AbpTenants A on T.TenantId = A.Id
left join AbpEditions E on A.EditionId = E.Id
left join TicketLinkedUsers TLU on TLU.TicketId = T.Id
left join TicketLinkTypes TLT on TLT.Id = TLU.TicketLinkTypeId
然而,我在最后四个连接中遇到了问题。
在项目中,我使用的是Ef Core。以下是我部分的实现方式:
var query = (from o in filteredTickets
join o1 in _productRepository.GetAll() on o.ProductId equals o1.Id into j1
from s1 in j1.DefaultIfEmpty()
join o2 in _productVersionRepository.GetAll() on o.ProductVersionId equals o2.Id into j2
from s2 in j2.DefaultIfEmpty()
join o3 in _ticketTypeRepository.GetAll() on o.TicketTypeId equals o3.Id into j3
from s3 in j3.DefaultIfEmpty()
join o4 in _ticketPriorityRepository.GetAll() on o.TicketPriorityId equals o4.Id into j4
from s4 in j4.DefaultIfEmpty()
join o5 in _ticketStateRepository.GetAll() on o.TicketStateId equals o5.Id into j5
from s5 in j5.DefaultIfEmpty()
join o6 in _tenantManager.Tenants on o.TenantId equals o6.Id into j6
from s6 in j6.DefaultIfEmpty()
// join o7 in _editionaRepository.GetAll() on s6.EditionId equals o7.Id into j7
// from s7 in j7.DefaultIfEmpty()
// join o8 in _ticketLinkedUsersRepository.GetAll() on o.Id equals o8.TicketId into j8
// from s8 in j8.DefaultIfEmpty()
// join o9 in _ticketLinkTypesRepository.GetAll() on s9.TicketLinkTypeId equals o9.Id into j9
// from s9 in j9.DefaultIfEmpty()
select new GetTicketForView() { Ticket = ObjectMapper.Map<TicketDto>(o)
, ProductName = s1 == null ? "" : s1.Name.ToString()
, ProductVersionName = s2 == null ? "" : s2.Name.ToString()
, TicketTypeName = s3 == null ? "" : s3.Name.ToString()
, TicketPriorityName = s4 == null ? "" : s4.Name.ToString()
, TicketState = ObjectMapper.Map<TicketStateTableDto>(s5)
, Tenant = ObjectMapper.Map<TenantShortInfoDto>(s6)
})
我将使用仓储模式接收数据。然后使用AutoMapper将所有数据映射到ViewModel中。这是我的ViewModel的样子:
public class GetTicketForView
{
public TicketDto Ticket { get; set; }
public TenantShortInfoDto Tenant { get; set; }
public string ProductName { get; set;}
public string ProductVersionName { get; set;}
public TicketStateTableDto TicketState { get; set; }
public string TicketTypeName { get; set;}
public string TicketPriorityName { get; set;}
public List<TicketLinkedUserDto> LinkedUsers { get; set; }
}
现在我正在尝试获取有关公司(AbpTenants)、版本(一对多关系)和TicketLinkedUsers列表(多对多关系)及其TicketLinkType信息的信息。模式: 我可以使用附加连接接收所有所需数据,但是我不知道如何正确地将数据绑定和映射到GetTicketForView。LinkedUsers和嵌套映射Edition的Tenant列表是一个问题。现在,为了使多对多关系起作用,我正在为每个票证进行单独的请求。
// execute to get tickets
tickets = await query
.OrderBy(input.Sorting ?? "ticket.id asc")
.PageBy(input)
.ToListAsync();
// then for each ticket get related users:
foreach (var ticket in tickets)
{
var linkedUsers = _ticketLinkedUsersRepository
.GetAllIncluding(lu => lu.TicketLinkType, lu => lu.User)
.OrderBy(a => a.TicketLinkType.Ordinal)
.Where(p => p.TicketId == ticket.Ticket.Id).ToList();
ticket.LinkedUsers = ObjectMapper.Map<List<TicketLinkedUserDto>>(linkedUsers);
}
这需要花费很多时间,因为我可以使用08和09一次请求获取所有数据,但我每张票都会发出额外的请求。我没有足够的经验来正确地处理它。
所以问题是,我如何使用linq实现第一个请求并将其映射到ViewModel?我应该使用额外的请求吗?或者使用Ef Core Api进行复杂请求会更好?或者在linq中映射复杂内容是不可能的?
提前感谢