efcore多表查询出错_利用EF Core的Lambda表达式和Join进行多表联动查询

#region 获取各个合作银行的意向单中金融分期申请情况

///

///获取各个合作银行的意向单中金融分期申请情况///

///

public ListGetReadyApplyStatistics(ReadyApplyStatisticsViewModel_Search searchmodel)

{

List list = new List();var banklist = db.N_LoanBank.Where(a => a.Sort != 0).OrderBy(a =>a.Sort).ToList();foreach (var item inbanklist)

{

ReadyApplyStatisticsViewModel model= newReadyApplyStatisticsViewModel();

model.BankName=item.BankName;var ReadyApplyQuery = db.N_LoanReadyApply.Where(a => a.BankId == item.BankId && a.CreateDate > searchmodel.ReadyApplyStartTime && a.CreateDate b.BankId == item.BankId && b.ApplyTime>searchmodel.ApplyStartTime && b.ApplyTime

{

ApplyQuery= ApplyQuery.Where(b=>b.BusinessType==searchmodel.BusinessType);

}if(!string.IsNullOrEmpty(searchmodel.ApplyChannel))

{

ApplyQuery= ApplyQuery.Where(b=>b.ApplyChannel.Contains(searchmodel.ApplyChannel));

}

model.ReadyApplyCount=ReadyApplyQuery.Count();

model.ApplyCount= ReadyApplyQuery.Join(ApplyQuery, a => a.BidderNo, b => b.BidderNo, (a, b) => new { BidderNo =a.BidderNo }).Count();

model.WaitAcceptCount= ReadyApplyQuery.Join(ApplyQuery.Where(b=>b.AcceptStatus==0 && b.CheckStatus == 0 && DateTime.Now <= b.AcceptExpiryTime), a => a.BidderNo, b => b.BidderNo, (a, b) => new { BidderNo =a.BidderNo }).Count();

model.AcceptCount= ReadyApplyQuery.Join(ApplyQuery.Where(b => b.AcceptStatus == 0 && b.CheckStatus == 1), a => a.BidderNo, b => b.BidderNo, (a, b) => new { BidderNo =a.BidderNo }).Count();

model.NotAcceptCount= ReadyApplyQuery.Join(ApplyQuery.Where(b => b.AcceptStatus == 2), a => a.BidderNo, b => b.BidderNo, (a, b) => new { BidderNo =a.BidderNo }).Count();

model.CheckCount= ReadyApplyQuery.Join(ApplyQuery.Where(b => b.AcceptStatus == 1 && b.CheckStatus==1), a => a.BidderNo, b => b.BidderNo, (a, b) => new { BidderNo =a.BidderNo }).Count();

model.NotCheckCount= ReadyApplyQuery.Join(ApplyQuery.Where(b => b.AcceptStatus == 1 && b.CheckStatus == 2), a => a.BidderNo, b => b.BidderNo, (a, b) => new { BidderNo =a.BidderNo }).Count();

model.CancelCheckCount= ReadyApplyQuery.Join(ApplyQuery.Where(b => b.AcceptStatus == 1 && b.CheckStatus == 3), a => a.BidderNo, b => b.BidderNo, (a, b) => new { BidderNo =a.BidderNo }).Count();

model.MoneyCheckCount= ReadyApplyQuery.Join(ApplyQuery.Where(b => b.AcceptStatus == 1 && b.CheckStatus == 1 && b.PaymentStatus==1 && b.MoneyCheckStatus==1), a => a.BidderNo, b => b.BidderNo, (a, b) => new { BidderNo =a.BidderNo }).Count();

model.NotMoneyCheckCount= ReadyApplyQuery.Join(ApplyQuery.Where(b => b.AcceptStatus == 1 && b.CheckStatus == 1 && b.PaymentStatus == 1 && b.MoneyCheckStatus == 0), a => a.BidderNo, b => b.BidderNo, (a, b) => new { BidderNo =a.BidderNo }).Count();

list.Add(model);

}

ReadyApplyStatisticsViewModel summodel= newReadyApplyStatisticsViewModel();

summodel.BankName= "总计";

summodel.ReadyApplyCount= list.Sum(a =>a.ReadyApplyCount);

summodel.ApplyCount= list.Sum(a =>a.ApplyCount);

summodel.WaitAcceptCount= list.Sum(a =>a.WaitAcceptCount);

summodel.AcceptCount= list.Sum(a =>a.AcceptCount);

summodel.NotAcceptCount= list.Sum(a =>a.NotAcceptCount);

summodel.CheckCount= list.Sum(a =>a.CheckCount);

summodel.NotCheckCount= list.Sum(a =>a.NotCheckCount);

summodel.CancelCheckCount= list.Sum(a =>a.CancelCheckCount);

summodel.MoneyCheckCount= list.Sum(a =>a.MoneyCheckCount);

summodel.NotMoneyCheckCount= list.Sum(a =>a.NotMoneyCheckCount);

list.Add(summodel);returnlist;

}#endregion