c# - 连接和分区方式中的嵌套选择

Question

我是 C# 的新手，我想在 Linq.plz 帮助中编写这个 TSql 代码。谢谢你

select a.Id,
       a.Date,
       b.Title CategoryTitle,
       a.Title,
       a.Description,
       a.Image 
  from (select *, 
               ROW_NUMBER() over(partition by CategoryID order by Date) rankno 
          from News) a
  join Categories b on a.CategoryID = b.Id
 where rankno <= 5

标签： c#linqtsql

Answer 1

假设您有新闻和类别的枚举：

var results = News.Join(Categories,         // Join News and Categories
                        a => a.CatergoryId, 
                        b => b.Id, 
                        (a,b) => new { News = a, Category = b}
                    )  
    .GroupBy(c => c.Category) // "partition by categoryId"
    .SelectMany(g => g.OrderBy(gd => gd.News.CreationDate)   // "order by Date"
                        .Take(5)    // RankNo <= 5
                        .Select(gdd => new {            // results
                                Id = gdd.News.Id, 
                                Date = gdd.News.Date, 
                                CategoryTitle = gdd.Category.Title,
                                Title = gdd.News.Title,
                                Description = gdd.News.Description, 
                                Image = gdd.News.Image
                            })
                );