这个sql语句在ling怎么打

[Yestin_]

select D.*,
   (select count(DISTRICT_ID) from [dbo].[STREET] 
      where DISTRICT_ID=D.ID) num
from [dbo].[DISTRICT] D 
order by num desc

[圣殿骑士18]

var query = from d in context.district
select new 
{
	d,
	count = context.street.Count(c=>c.district_id == d.id)
}

[q107770540]

var query = (from d in context.district
                    let temp = context.street.Where(c=>c.district_id == d.id).Select(c=>c.district_id).Count()
                    select new 
                    {
                        d,
                        num = temp
                    }).OrderByDescending(x=>x.num);

[小鑫心]

 DISTRICTList.Select(x => new { x, num = STREETList.Count(y => y.DISTRICT_ID == x.D.ID) }).ToList();