获取按日期排序的不同列

Question

我有以下两个表：

Job Title | PostDate | CompanyId
Assitant  | 12/15/10 | 10
Manager   | 12/1/10  | 11
Developer | 12/31/10 | 10
Assitant  | 12/1/10  | 13
PM        | 11/29/10 | 12

CompanyId | Name
10        | Google
11        | Yahoo
12        | Microsoft
13        | Oracle

现在我想要 3 家不同的公司，其职位按发布日期排序。结果表如下：

Job Title | PostDate | CompanyName
Developer | 12/31/10 | Google
Manager   | 12/1/10  | Yahoo
Assitant  | 12/1/10  | Oracle

如何使用 linq 查询实现这一点？任何帮助将不胜感激...

Answer 1

我认为这会是这样的：

var query = from company in db.Companies
            join job in db.Jobs on company.CompanyId equals job.CompanyId
            group job by company into jobsByCompany
            let lastJob = jobsByCompany.OrderByDescending(x => x.PostDate)
                                       .First()
            orderby lastJob.PostDate descending
            select new
            {
                JobTitle = lastJob.JobTitle,
                PostDate = lastJob.PostDate,
                CompanyName = jobsByCompany.Key.Name
            };

先进行连接然后再进行分组有点奇怪 - 我们可以先进行 GroupJoin，然后丢弃空选项：

var query = from company in db.Companies
            join job in db.Jobs on company.CompanyId equals job.CompanyId
                 into jobsByCompany // Make this a group join
            let lastJob = jobsByCompany.OrderByDescending(x => x.PostDate)
                                       .FirstOrDefault()
            where lastJob != null
            orderby lastJob.PostDate descending
            select new
            {
                JobTitle = lastJob.JobTitle,
                PostDate = lastJob.PostDate,
                CompanyName = company.Name
            };

编辑：请注意，这不仅仅需要前三名的结果。使用 query = query.Take(3); 仅获取前三个结果。