linq 多表分组排序查询
0
悬赏园豆：5
[已解决问题]
解决于 2014-03-06 16:02
我只知道用sql写，但是，用linq sql我不知道怎么写
sql是这样的:
select sp.Id,sp.Name,pc.Name,sp.LoveNum,COUNT(sm.SightId) as 评论数 from SightPoint sp
join SightPointClass sc on sc.Id=sp.ClassId
join SightPointComm sm on sm.SightId=sp.Id
join SightPointPic si on si.SightId=sp.Id
join PicInfo pc on pc.Id=si.PicInfoId
where pc.Mark='2'
group by ClassId,sp.Id,sp.Name,pc.Name,sp.LoveNum
order by LoveNum desc
linq sql?谁知道呢？
最佳答案
0
已经搞定，呵呵
既然搞定了，应该把 SQL 语句贴出来。
其他回答(1)
-1
一样的
from sp in SightPoint
join sc in SightPointClass on sc.Id equals sp.ClassId
收获园豆：5
大哥，我并不是没写，只是有个地方卡主了，就是关于那个评论数，就是有个评论表，它跟景点表是多对一的关系，
var data = (from sightpoint in sightService.GetList()
join sightpic in sightPicService.GetList() on sightpoint.Id equals sightpic.SightId
join pic in openPicService.GetAllPic() on sightpic.PicInfoId equals pic.Id
join comm in sightCommService.GetList() on sightpoint.Id equals comm.SightId
join cla in sightClassService.GetList() on sightpoint.ClassId equals cla.Id
where pic.Mark=="2"
group sightpoint by new
{
sightpoint.Id,
comm.SightName,
sightpoint.ClassId,
sightpoint.LoveNum,
pic.Name
} into g
orderby g.Key.LoveNum
select new Application.Admin.Dto.OpentSightList
{
Id = g.Key.Id,
Name = g.Key.SightName,
PicName = g.Key.Name,
CommCount = g.Count(),
LoveNum = g.Key.LoveNum
});
@魔女小溪: 实在不行G.Count()使用子查询 虽然会慢N倍。。。
@组织万岁: 我这个就是正确的
@魔女小溪: 话说你干嘛用EF做这么复杂的查询?分成几个部分不行吗?
