LINQ 联合查询

    List<Attachment> imgList = (from a in ZQSDWEBEntities.Attachment
                                        join m in
                                            (from t in ZQSDWEBEntities.MessageDetail
                                             where (id).Contains(t.ParentID)
                                             orderby t.MessageDate descending, t.MessageStart descending
                                             select t)
                                            on a.MessageId equals m.MessageId
                                        select a
                                          ).ToList();

中间这部分没生效

改成这样就可以了 

     //最新10条数据(按MessageStart降序排序)
            var topMessage = (from t in ZQSDWEBEntities.MessageDetail
                              where (id).Contains(t.ParentID)
                              orderby t.MessageStart descending, t.MessageDate descending
                              select t).Skip(0).Take(10);
            //最新10条数据的图片
            List<Attachment> imgList = (from a in ZQSDWEBEntities.Attachment
                                        join m in topMessage
                                            on a.MessageId equals m.MessageId
                                        select a
                                          ).ToList();

 

生成  sql:

 

SELECT
[Extent1].[AttachmentId] AS [AttachmentId],
[Extent1].[Parth] AS [Parth],
[Extent1].[ParthTitle] AS [ParthTitle],
[Extent1].[MessageId] AS [MessageId],
[Extent1].[remark] AS [remark],
[Extent1].[Type] AS [Type]
FROM  [dbo].[Attachment] AS [Extent1]
INNER JOIN  (SELECT TOP (10) [Filter1].[MessageId] AS [MessageId], [Filter1].[MessageDate] AS [MessageDate], [Filter1].[MessageStart] AS [MessageStart]
 FROM ( SELECT [Extent2].[MessageId] AS [MessageId], [Extent2].[MessageDate] AS [MessageDate], [Extent2].[MessageStart] AS [MessageStart], row_number() OVER (ORDER BY [Extent2].[MessageStart] DESC, [Extent2].[MessageDate] DESC) AS [row_number]
  FROM [dbo].[MessageDetail] AS [Extent2]
  WHERE [Extent2].[ParentID] IN (48,210,211)
 )  AS [Filter1]
 WHERE [Filter1].[row_number] > 0
 ORDER BY [Filter1].[MessageStart] DESC, [Filter1].[MessageDate] DESC ) AS [Limit1] ON [Extent1].[MessageId] = [Limit1].[MessageId]