关于linq的两个问题

[Mic_Gary]

通过linq查询出如下结果，

实现的语句为：

var result1 = from a in t.T_BaseBookInfo
                              join b in t.T_BorrowInfoHsty  //表
                              on a.GUID equals b.BookID
                              join c in t.T_BaseBookInfo   //表
                              on b.BookID equals c.GUID
                              join d in t.T_Press 
                              on c.PressCode equals d.PressCode
                              select new BorrowRankingModel   //BorrowRankingModel 为自建模型
                              {
                                  GUID = b.BookID,
                                  PressName=d.PressName,
                                  Author=c.Author,
                                  BookTitle=c.BookTitle
                              };

我想根据最后一列绑定的字段（字段为“GUID”）分组，把guid相同的合并成一条，“流通次数”那列中放入条数，并从大到小排列，在第一列写入1,2,3,4……序号（这个序号的问题我发现用个 ++ 的方法不行这个方法我行不通）

[Andy__Huang]

;with cte as
(
select *
from (select *,rn=ROW_NUMBER() OVER(partition by GuidColumn6 order by getdate()) from T_BaseBookInfo )t
)

select a.正题名,a.作者,a.出版社,
stuff((select ','+GuidColumn6 from cte b 
       where b.GuidColumn6=a.GuidColumn6 and b.name=a.name 
       for xml path('')),1,1,'') 'GuidColumn6'
from cte a
group by  a.正题名,a.作者,a.出版社

最好在数据里先做好统计再返回结果。linq虽然可以做分组，但木有那么方便。

[q107770540]

var result1 = (from a in t.T_BaseBookInfo
                              join b in t.T_BorrowInfoHsty  //表
                              on a.GUID equals b.BookID
                              join c in t.T_BaseBookInfo   //表
                              on b.BookID equals c.GUID
                              join d in t.T_Press 
                              on c.PressCode equals d.PressCode  
                              select new {b,c}).GroupBy(x=>x.GUID)
                             .Select((g,i)=> new BorrowRankingModel   //BorrowRankingModel 为自建模型
                              {
                                  排名=i+1,
                                  GUID = g.Key,
                                  流通次数=g.Count(),
                                  PressName=g.First().PressName,
                                  Author=g.First().Author,
                                  BookTitle=g.First().BookTitle
                              };

[q107770540]

var result1 = (from a in t.T_BaseBookInfo
                              join b in t.T_BorrowInfoHsty  //表
                              on a.GUID equals b.BookID
                              join c in t.T_BaseBookInfo   //表
                              on b.BookID equals c.GUID
                              join d in t.T_Press 
                              on c.PressCode equals d.PressCode 
                              select new BorrowRankingModel  
                              {
                                  GUID = b.BookID,
                                  PressName=d.PressName,
                                  Author=c.Author,
                                  BookTitle=c.BookTitle
                              }).GroupBy(x=>x.GUID)
                             .Select((g,i)=> new BorrowRankingModel   //BorrowRankingModel 为自建模型
                              {
                                  排名=i+1,
                                  GUID = g.Key,
                                  流通次数=g.Count(),
                                  PressName=g.First().PressName,
                                  Author=g.First().Author,
                                  BookTitle=g.First().BookTitle
                              };

[Mic_Gary]

引用 3 楼 q107770540 的回复:

大侠，我运行的时候变这样了

[q107770540]

以为你的是LINQ2SQL， LINQ2EF不支持SELECT的重载方法，可以这样： var result1 = (from a in t.T_BaseBookInfo join b in t.T_BorrowInfoHsty //表 on a.GUID equals b.BookID join c in t.T_BaseBookInfo //表 on b.BookID equals c.GUID join d in t.T_Press on c.PressCode equals d.PressCode select new BorrowRankingModel { GUID = b.BookID, PressName=d.PressName, Author=c.Author, BookTitle=c.BookTitle }).AsEnumberable().GroupBy(x=>x.GUID) .Select((g,i)=> new BorrowRankingModel { 排名=i+1, GUID = g.Key, 流通次数=g.Count(), PressName=g.First().PressName, Author=g.First().Author, BookTitle=g.First().BookTitle };

[Mic_Gary]

引用 5 楼 q107770540 的回复:

以为你的是LINQ2SQL， LINQ2EF不支持SELECT的重载方法，可以这样： var result1 = (from a in t.T_BaseBookInfo join b in t.T_BorrowInfoHsty //表 on a.GUID equals b.BookID join c in t.T_BaseBookInfo //表 on b.BookID equals c.GUID join d in t.T_Press on c.PressCode equals d.PressCode select new BorrowRankingModel { GUID = b.BookID, PressName=d.PressName, Author=c.Author, BookTitle=c.BookTitle }).AsEnumberable().GroupBy(x=>x.GUID) .Select((g,i)=> new BorrowRankingModel { 排名=i+1, GUID = g.Key, 流通次数=g.Count(), PressName=g.First().PressName, Author=g.First().Author, BookTitle=g.First().BookTitle };

版主！！！你好强悍呀，收我为徒怎么样？！

[Mic_Gary]

引用 5 楼 q107770540 的回复:

版主大人，帮我看看吧 http://bbs.csdn.net/topics/390609879

[鱼缸里的小泥鳅]

引用 楼主 Mic_Gary 的回复:

通过linq查询出如下结果， 实现的语句为：

var result1 = from a in t.T_BaseBookInfo
                              join b in t.T_BorrowInfoHsty  //表
                              on a.GUID equals b.BookID
                              join c in t.T_BaseBookInfo   //表
                              on b.BookID equals c.GUID
                              join d in t.T_Press 
                              on c.PressCode equals d.PressCode
                              select new BorrowRankingModel   //BorrowRankingModel 为自建模型
                              {
                                  GUID = b.BookID,
                                  PressName=d.PressName,
                                  Author=c.Author,
                                  BookTitle=c.BookTitle
                              };

我想根据最后一列绑定的字段（字段为“GUID”）分组，把guid相同的合并成一条，“流通次数”那列中放入条数，并从大到小排列，在第一列写入1,2,3,4……序号（这个序号的问题我发现用个 ++ 的方法不行这个方法我行不通）

真是高手啊，学习了哈哈，真厉害

[Mic_Gary]

引用 5 楼 q107770540 的回复:

大神！！！http://bbs.csdn.net/topics/390620485