【SqlSugar】联合查询、分组，二个Queryable的Join

sqlsugar的关联查询，mysql查询转sqlsugar，记录下，供下次遇到查阅

mysql：

select * from (
select a.MchId,a.mchs,b.enables from (
select MchId, COUNT(MchId) mchs from mchchannelinfo where MchId in(select MchId from mchchannelinfo where InputState = 1 and Channel != 4) and Channel !=4 GROUP BY MchId
-- ORDER BY MchId
) a
INNER JOIN
(
select MchId, COUNT(IsEnable) enables from mchchannelinfo where MchId in(select MchId from mchchannelinfo where InputState = 1 and Channel != 4) and Channel !=4 and IsEnable=0 GROUP BY MchId
-- ORDER BY MchId
) b
on a.MchId = b.MchId
) c where c.mchs = c.enables;

 

sqlsugar：

var success_mchs = _sqlSugarClient.Queryable<MchChannelInfo>().Where(s => s.InputState == InputStateEnum.成功 && s.Channel != ChannelEnum.云商通).Select(s => s.MchId).ToList();
var mchs = _sqlSugarClient.Queryable<MchChannelInfo>().Where(s => s.Channel != ChannelEnum.云商通 && success_mchs.Contains(s.MchId)).GroupBy(s => s.MchId).Select(s => new MchInnerView { MchId = s.MchId, ids = SqlFunc.AggregateCount(s.MchId) });
var enables = _sqlSugarClient.Queryable<MchChannelInfo>().Where(s => s.Channel != ChannelEnum.云商通 && success_mchs.Contains(s.MchId) && s.IsEnable == false).GroupBy(s => s.MchId).Select(s => new MchInnerView { MchId = s.MchId, ids = SqlFunc.AggregateCount(s.IsEnable) });

 

//注：两个querable的关联两个querable需要是实体
var input = _sqlSugarClient.Queryable(mchs, enables, (a, b) => a.MchId == b.MchId).Where((a,b)=>a.ids == b.ids).Select((a, b) => a.MchId).ToList();