LINQ又没人 发过来看看 EF查询问题 3Q

[xiekai12342010]

表一 发帖表 groupPosts
ID TITLE CONTENT AddTime
1 AAA AAA 2013-6-28 09:00
2 BBB BBB 2013-6-28 14:00
3 CCC CCC 2013-6-28 10:00
4 DDD DDD 2013-6-28 12:00
5 EEE EEE 2013-6-28 13:00

表二 回复表 Comments
ID TargetID CONTENT ADDTIME
1 1 AAA 2013-6-28 11:00
2 1 AAA 2013-6-28 10:00
3 2 BBB 2013-6-28 14:00
4 3 CCC 2013-6-28 10:00
5 1 AAA 2013-6-28 09:00
6 4 DDD 2013-6-28 12:00
7 3 CCC 2013-6-28 13:00



先按照Comments表 ADDTIME 最新时间（1个帖子对应MAX ADDTIME就可以了）排序，再按照groupPosts表 AddTime进行排序 该怎么查询呢
查询结果：
发帖表 groupPosts
ID TITLE CONTENT
2 BBB BBB
3 CCC CCC
4 DDD DDD
1 AAA AAA
5 EEE EEE

请指点下 谢谢 现在写的LINQ是这样的 但是结果不对
var query =
from p in
context.groupPosts
join c in
context.Comments.GroupBy(x => x.TargetID)
.Select(g => new { TargetID = g.Key, ADDTIME = g.Max(y => y.AddTime) })
on p.ID equals c.TargetID into leftGroup
from c in leftGroup.DefaultIfEmpty()
orderby c.ADDTIME descending, p.AddTime descending
select p;

[q107770540]

你既然使用了left join，就应该知道，有的时候，Comments表里是没有数据的：

var query =
                        from p in
                            context.groupPosts
                        join c in
                            context.Comments.GroupBy(x => x.TargetID)
                                   .Select(g => new { TargetID = g.Key, ADDTIME = g.Max(y => y.AddTime) })
                            on p.ID equals c.TargetID into leftGroup
                        from c in leftGroup.DefaultIfEmpty()
                        orderby c==null?DateTime.MinValue:c.ADDTIME descending, p.AddTime descending
                        select p;

[xiekai12342010]

能确定Comments表里是有数据的 发帖的时候就加入了一条数据

[q107770540]

						from p in this.GroupPosts
						join c in 	this.Comments 
							on p.ID equals c.TargetID into  Group
						let com=Group.OrderByDescending(x=>x.ADDTIME).FirstOrDefault()
						orderby com.ADDTIME descending, p.AddTime descending
						select p

这样测试成功，得到了你帖子中想要的结果： ID TITLE CONTENT 2 BBB BBB 3 CCC CCC 4 DDD DDD 1 AAA AAA 5 EEE EEE 这是LINQ语句生成的SQL语句：

SELECT [t0].[ID], [t0].[TITLE], [t0].[CONTENT], [t0].[AddTime]
FROM [groupPosts] AS [t0]
ORDER BY (
    SELECT [t2].[ADDTIME]
    FROM (
        SELECT TOP (1) [t1].[ADDTIME]
        FROM [Comments] AS [t1]
        WHERE ([t0].[ID]) = [t1].[TargetID]
        ORDER BY [t1].[ADDTIME] DESC
        ) AS [t2]
    ) DESC, [t0].[AddTime] DESC

[q107770540]

这是我用的测试数据：

INSERT  [dbo].[groupPosts] SELECT 1,'AAA','AAA','2013-6-28 09:00'  
INSERT  [dbo].[groupPosts] SELECT 2,'BBB','BBB','2013-6-28 14:00'   
INSERT  [dbo].[groupPosts] SELECT 3,'CCC','CCC','2013-6-28 10:00' 
INSERT  [dbo].[groupPosts] SELECT 4,'DDD','DDD','2013-6-28 12:00'  
INSERT  [dbo].[groupPosts] SELECT 5,'EEE','EEE','2013-6-28 13:00' 


INSERT Comments SELECT 1,1,'AAA','2013-6-28 11:00' 
INSERT Comments SELECT 2,1,'AAA','2013-6-28 10:00' 
INSERT Comments SELECT 3,2,'BBB','2013-6-28 14:00' 
INSERT Comments SELECT 4,3,'CCC','2013-6-28 10:00' 
INSERT Comments SELECT 5,1,'AAA','2013-6-28 09:00' 
INSERT Comments SELECT 6,4,'DDD','2013-6-28 12:00' 
INSERT Comments SELECT 7,3,'CCC','2013-6-28 13:00' 

[xiekai12342010]

3Q 版主大大