如何通过EF、LINQ按关联表最新ShortName排序Company列表？

问题背景 #

现有EF实体模型如下：

public class Company
{ 
    public long Id {get; set;}
    // 其他字段
    public List<OrganisationRegData> RegData { get; set; }
}

public class OrganisationRegData
{
    public long Id {get; set;}

    [MaxLength(250)]
    public string FullName { get; set; }

    [MaxLength(250)]
    public string ShortName { get; set; }

    // 其他字段

    public Company Company { get; set; }
}

需求：获取Company列表，并按其关联的最新OrganisationRegData的ShortName字段排序。用户提供的参考SQL如下：

select * 
from Companies c 
left join OrganisationRegData ord on c.ID = ord.CompanyID
order by ord.ShortName

注意：上述参考SQL会返回重复的Company记录（当一个Company对应多条OrganisationRegData时），且未筛选出"最新"的关联记录，以下是符合需求的EF LINQ实现方案。

实现方案 #

方案1：导航属性+子查询筛选最新记录 #

通过子查询获取每个Company对应的最新OrganisationRegData（假设Id越大表示记录越新），再以此为依据排序：

var companies = await _context.Companies
    .Select(c => new 
    {
        Company = c,
        LatestRegData = c.RegData.OrderByDescending(rd => rd.Id).FirstOrDefault()
    })
    .OrderBy(x => x.LatestRegData.ShortName)
    .Select(x => x.Company)
    .ToListAsync();

方案2：GroupJoin关联并筛选最新记录 #

如果需要直接操作关联表，也可以用GroupJoin分组后取最新记录：

var companies = await _context.Companies
    .GroupJoin(
        _context.OrganisationRegData,
        c => c.Id,
        rd => rd.Company.Id,
        (c, rdList) => new 
        {
            Company = c,
            LatestRegData = rdList.OrderByDescending(rd => rd.Id).FirstOrDefault()
        })
    .OrderBy(x => x.LatestRegData.ShortName)
    .Select(x => x.Company)
    .ToListAsync();

方案3：原生SQL查询（匹配参考SQL逻辑） #

如果要完全匹配用户提供的SQL逻辑（即使会返回重复Company），可以直接执行原生SQL：

var companies = await _context.Companies
    .FromSqlRaw(@"
        select c.* 
        from Companies c 
        left join OrganisationRegData ord on c.ID = ord.CompanyID
        order by ord.ShortName")
    .ToListAsync();

注意：此写法会返回重复的Company实例，仅当业务允许重复结果时使用。

关键说明 #

• 假设OrganisationRegData.Id递增，OrderByDescending(rd => rd.Id)可筛选出最新记录；如果有专门的时间字段（如CreateTime），替换为该字段即可。
• 使用FirstOrDefault()确保无关联OrganisationRegData的Company也能被保留，对应SQL的LEFT JOIN逻辑。
• 最终通过Select(x => x.Company)提取纯净的Company列表，避免返回匿名类型。

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