已下sql 转为linq to sql (各位大侠路过看看)

[Lee_Tiger]

select 
      top 10 
	  s.ID,s.CreateTime,
	  count(sc.ID) SCount,
	  count(p.ID) PCount,
	  count(r.ID) RCount
from  
	 s 
	 left join  sc on s.id=sc.SubjectID
	 left join  p  on s.id=p.SubjectID
	 left join  r  on s.id=r.SubjectID
group by
      s.ID,s.CreateTime
order by   s.CreateTime desc
     

[q107770540]

没时间写了，参考这个吧： http://bbs.csdn.net/topics/390419148

[Lee_Tiger]

[code=csharp] var data = from s in dbContent.[s] join sc in dbContent.[sc] on s.ID equals sc.SubjectID into scdata join p in dbContent.[p] on s.ID equals p.SubjectID into pdata join r in dbContent.[r] on s.ID equals r.SubjectID into rdata orderby s.CreateTime descending select new { s.ID, SCount = scdata.Count(), PCount = pdata.Count(), RCount = rdata.Count() }; //data 对象为Null值 因为 [sc],[p],[r]表 有些是空的 [code]

[Lee_Tiger]

没人来看啊 我自己顶！

[Lee_Tiger]

Ok 已解决了！ 非常感谢2楼的资料！

          var data = (from s in dbContent.[s]
                       join sc in dbContent.[sc] on s.ID.ToString() equals sc.SubjectID.ToString() into scdata
                       from sc in scdata.DefaultIfEmpty()
                       join p in dbContent.[p] on s.ID.ToString() equals p.SubjectID.ToString() into pdata
                       from p in pdata.DefaultIfEmpty()
                       join r in dbContent.[r] on s.ID.ToString() equals r.SubjectID.ToString() into rdata
                       from r in rdata.DefaultIfEmpty()
                       orderby s.CreateTime descending
                       select new
                       {
                           s.ID,
                           SCount = scdata.Count(),
                           PCount = pdata.Count(),
                           RCount = rdata.Count()
                       }).Skip(0).Take(10);

[q107770540]

.Skip(0) 就不要了，多此一举