sql to linq的写法，数据库为mysql,开发语言c#

[im_yh]

select a.ID,A.NAME, w.online,w.offline,w.sum2  from schools a
LEFT JOIN 
(SELECT school_id,
 sum(if (work_status = 1,1,0)) AS online,
 sum(if (work_status = 2,1,0)) AS offline,
 count( work_status ) AS sum2
 FROM devices
GROUP BY school_id) w on  a.id = w.school_id
where   a.name like '%北京%';

[丰云]

到底想说什么？？？？？？？？

问个问题，搞得人莫名其妙的。。。。。

[圣殿骑士18]

这个复杂度的sql，不建议用linq写。写出linq会很复杂难读，而且最终翻译的sql不如原始sql简洁，执行的效率也不高。

[threenewbee]

from r in (from a in schools
join w in devices.GroupBy(x => x.school_id).Selext(x => {
school_id = x.Key,
online = x.Where(y => y.work_status== "1,1,0").Sum(y => y.work_status), //这里没看懂，不一定对，什么1,1,0
offline = x.Where(y => y.work_status== "2,1,0").Sum(y => y.work_status),
sum2 = x.Count() })
on a.id equals w.school_id
select new { a.ID,A.NAME, w.online,w.offline,w.sum2 })
where x.name.Contains("北京");

[threenewbee]

where x.name.Contains("北京");
->
where r.name.Contains("北京");