LINQ多表、复杂查询

生成sql一样：

sql:
select BSCode [Key],BSName [Value] from Table1 
where BSCode in (select distinct BSCode from Table2 where RoleCode='6010')

linq:
var resDatas = await (from bs in _context.Table1.AsNoTracking()
    where (from su in _context.Table2.AsNoTracking()
            where su.RoleCode == roleCode
            select su.Bscode).Distinct().Contains(bs.Bscode)
    select new KeyValueModel { Key = bs.Bscode, Value = bs.Bsname }
).ToListAsync(cancelToken);

var resDatas = await _context.Table1.AsNoTracking().Where(m =>
    _context.Table2.AsNoTracking()
    .Where(a => a.RoleCode == roleCode).Select(a => a.Bscode).Distinct()
    .Contains(m.Bscode)
).Select(m => new KeyValueModel { Key = m.Bscode, Value = m.Bsname }).ToListAsync(cancelToken);

一个相等查询备忘

var ppsMatches = await (
    from pps in _context.ProjectProcessSchedules
    join dp in _context.DepmtProcesses on pps.DepmtProcessId equals dp.Id
    join p in _context.ProjectBases on pps.ProjectBaseId equals p.Id
    join pid in _context.ProjectsInDepmts on p.Id equals pid.ProjectBaseId
    where pps.IsDeleted == false && pps.AppId == appidOptions && pps.AssignedUserCode == userCode && pps.Month == month && pid.DepmtCode == depmtCode && p.Status == ProjectBaseStatusOptions.Running
    select new UserMonthDepmtProjectProcessScheduleModel
    {
        ProjectBase = _mapper.Map<ProjectBaseDto>(p),
        ProjectInDepmt = _mapper.Map<ProjectsInDepmtsDto>(pid),
        DepmtProcess = dp,
        ProjectProcessSchedule =  _mapper.Map<ProjectProcessScheduleDto>(pps),
    }
    ).Distinct().ToListAsync();

if (ppsMatches == null || !ppsMatches.Any()) return null;

var resModel = new List<UserMonthDepmtProjectProcessScheduleResponse>();

foreach (var item in ppsMatches)
{
    var userMonthDepmtProjectProcessScheduleResponse = new UserMonthDepmtProjectProcessScheduleResponse{
        ProjectBase = item.ProjectBase,
        ProjectInDepmt = item.ProjectInDepmt
    };
    var processSchedules = new List<ProcessForUserMonthDepmtProjectProcessScheduleResponse>();

    //var depmtProcessInfo = await _generalRepository.FindOneAsync<DepmtProcessInfo>(m=>m.Id.Equals(item.ProjectProcessSchedule.DepmtProcessId));
    
    var processForUserMonthDepmtProjectProcessScheduleResponse = new ProcessForUserMonthDepmtProjectProcessScheduleResponse{
        DepmtProcessId = item.DepmtProcess.Id,
        DepmtProcessName = item.DepmtProcess.Name,
    };
    //var reviewSteps = new List<ProjectProcessScheduleDto>();
    var reviewStepsInProjectProcessSchedules = ppsMatches.Where(m=>m.ProjectProcessSchedule.ProjectBaseId.Equals(item.ProjectProcessSchedule.ProjectBaseId) && m.ProjectProcessSchedule.DepmtProcessId.Equals(item.ProjectProcessSchedule.DepmtProcessId)).OrderBy(m=>m.ProjectProcessSchedule.ReviewStep).Select(m=>m.ProjectProcessSchedule);
    //var reviewStepsInProjectProcessSchedules = await _generalRepository.FindAsync<ProjectProcessScheduleInfo>(m=>m.ProjectBaseId.Equals(item.ProjectProcessSchedule.ProjectBaseId) && m.DepmtProcessId.Equals(item.ProjectProcessSchedule.DepmtProcessId),new SortModel{ColumnName="ReviewStep"});
    //var reviewSteps = _mapper.Map<IEnumerable<ProjectProcessScheduleDto>>(reviewStepsInProjectProcessSchedules);
    var reviewSteps = reviewStepsInProjectProcessSchedules;
    processForUserMonthDepmtProjectProcessScheduleResponse.ReviewSteps = reviewSteps;

    processSchedules.Add(processForUserMonthDepmtProjectProcessScheduleResponse);

    userMonthDepmtProjectProcessScheduleResponse.ProcessSchedules = processSchedules;

    resModel.Add(userMonthDepmtProjectProcessScheduleResponse);
}


上述相同结果语句类似：
var resModel = await (
    from pps in _context.ProjectProcessSchedules
    join dp in _context.DepmtProcesses on pps.DepmtProcessId equals dp.Id
    join p in _context.ProjectBases on pps.ProjectBaseId equals p.Id
    join pid in _context.ProjectsInDepmts on pps.ProjectBaseId equals pid.ProjectBaseId
    where pps.IsDeleted == false && pps.AppId == appidOptions && pps.AssignedUserCode == userCode && pps.Month == month && pid.DepmtCode == depmtCode && p.Status == ProjectBaseStatusOptions.Running
    orderby dp.Sort
    select new UserMonthDepmtProjectProcessScheduleResponse
    {
        ProjectBase = _mapper.Map<ProjectBaseDto>(p),
        ProjectInDepmt = _mapper.Map<ProjectsInDepmtsDto>(pid),
        ProcessSchedules = (from pda in _context.DepmtProcesses
                            join dida in _context.ProjectsInDepmts on pda.DepmtCode equals dida.DepmtCode
                            where pps.DepmtProcessId.Equals(pda.Id) && pps.ProjectBaseId.Equals(dida.ProjectBaseId)
                            orderby pda.Sort
                            select new ProcessForUserMonthDepmtProjectProcessScheduleResponse
                            {
                                DepmtProcessId = pda.Id,
                                DepmtProcessName = pda.Name,
                                ReviewSteps = (from ppsa in _context.ProjectProcessSchedules
                                                where ppsa.DepmtProcessId.Equals(pda.Id) && ppsa.ProjectBaseId.Equals(dida.ProjectBaseId)
                                                orderby ppsa.ReviewStep
                                                select _mapper.Map<ProjectProcessScheduleDto>(ppsa)
                                ).Distinct()
                            }).Distinct()
    }

).Distinct().ToListAsync();