求助：将按PR_NUMBER取最新记录的SQL语句转换为LINQ

1. 先提取每个PR_NUMBER对应的最新日期 #

先通过分组得到每个PR_NUMBER对应的最大PR_ID_CTGY_DATE，这是实现原SQL筛选逻辑的核心：

var latestPRDates = dbContext.tb_pr_mst_record
    .GroupBy(m => m.PR_NUMBER)
    .Select(g => new 
    {
        PR_NUMBER = g.Key,
        LatestDate = g.Max(m => m.PR_ID_CTGY_DATE)
    });

2. 关联主从表并应用筛选 #

用上面得到的最新日期组合作为筛选条件，关联主表和明细表，得到目标结果：

方法语法写法 #

var finalResult = dbContext.tb_pr_mst_record
    // 筛选出每个PR_NUMBER的最新记录
    .Join(latestPRDates, 
          master => new { master.PR_NUMBER, master.PR_ID_CTGY_DATE }, 
          latest => new { latest.PR_NUMBER, latest.LatestDate }, 
          (master, latest) => master)
    // 关联明细表
    .Join(dbContext.tb_pr_dtl_record, 
          master => master.PR_ID, 
          detail => detail.PR_ID, 
          (master, detail) => new 
          {
              MasterRecord = master,
              DetailRecord = detail
          });

查询语法写法 #

var finalResult = from master in dbContext.tb_pr_mst_record
                  join latest in latestPRDates 
                      on new { master.PR_NUMBER, master.PR_ID_CTGY_DATE } equals new { latest.PR_NUMBER, latest.LatestDate }
                  join detail in dbContext.tb_pr_dtl_record 
                      on master.PR_ID equals detail.PR_ID
                  select new 
                  {
                      MasterRecord = master,
                      DetailRecord = detail
                  };

3. 单条语句整合版（可选） #

如果不想拆分变量，可将分组逻辑直接嵌入查询：

var finalResult = from master in dbContext.tb_pr_mst_record
                  join latest in (
                      from m in dbContext.tb_pr_mst_record
                      group m by m.PR_NUMBER into g
                      select new { PR_NUMBER = g.Key, LatestDate = g.Max(x => x.PR_ID_CTGY_DATE) }
                  ) on new { master.PR_NUMBER, master.PR_ID_CTGY_DATE } equals new { latest.PR_NUMBER, latest.LatestDate }
                  join detail in dbContext.tb_pr_dtl_record on master.PR_ID equals detail.PR_ID
                  select new { MasterRecord = master, DetailRecord = detail };

关键注意点 #

• LINQ不支持SQL式的(列1,列2) IN (...)写法，这里通过匿名类型结构匹配实现等价逻辑，必须保证匿名类型的属性名、类型完全一致
• 如果你的实体类属性名或命名空间有差异，需要对应调整匿名类型的字段
• 若不需要匿名类型返回，可将select部分替换为你自定义的实体类投影

内容的提问来源于stack exchange，提问作者BlackSD