linq left join group by count组合统计,防止count()为null结果为1的错误。
原生sql
select car.id,carnum,count(carplan.carid) as timeLen from tab_car as car join tab_inComeType as income on car.inComeTypeId=income.Id and inComeTypeId=1 and stateCode=1 and carTypeId=1 left join tab_carPlan as carplan on car.id=carplan.carId and carplan.planDate='2023-7-6' and carplan.isCancel=0 group by carnum,car.id order by car.id asc
结果:
linq 写法:
var list = from car in carList.Where(e => e.inComeTypeId == incomeTypeId && e.stateCode == (int)stateEnum.有效 && e.carTypeId == carTypeId) join income in incomeList on car.inComeTypeId equals income.Id join carplan in carPlanList.Where(e => e.planDate == planDate && e.isCancel == 0) on car.id equals carplan.carId into temp from tt in temp.DefaultIfEmpty() group tt by new { car.carNum, car.id } into g orderby g.Key.carNum ascending select new { carId = g.Key.id, carNum = g.Key.carNum, timeLen = g.Count(e => e != null) //防止为null时统计结果为1 };
api输出结果
人生三苦:读书、赶马、磨豆腐。每天努力进步,加油奥利给!