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输出结果

人生三苦：读书、赶马、磨豆腐。每天努力进步，加油奥利给！