如何通过LINQ根据EmployeeInfoId获取对应职位名称？

模型类定义 #

public partial class EmployeeInfo
{
    public int     EmployeeInfoId   { get; set; }
    public string  FirstName        { get; set; } = null!;
    public int?    JobTitleLookupId { get; set; }

    public virtual JobTitleLookup? JobTitleLookup { get; set; }
}

public partial class JobTitleLookup
{
    public int    JobTitleLookupId { get; set; }
    public string Title            { get; set; } = null!;
    
    public virtual ICollection<EmployeeInfo> EmployeeInfos { get; } = new List<EmployeeInfo>();
}

数据表数据 #

JobTitleLookup表 #

JobTitleLookupID     title
1                   title1
2                   title2
3                   title3

EmployeeInfo表 #

EmployeeInfoId FirstName JobTitleLookupID
1                test1     2
2                test3     1
3                 testx     3

用户现有未完成代码 #

public async Task<List<string?>> GetJobTitle(string employeeId)
{
    var query = _ackContext.EmployeeInfos
        .Include(c => c.JobTitleLookup)
        .Where(c => c.EmployeeNumber == employeeId)
}

目标SQL语句 #

SELECT
    title
FROM
    employeeInfo e
    INNER JOIN JobTitleLookup j ON
        e.JobTitleLookupID =j.JobTitleLookupID
        AND
        EmployeeInfoID = 212;

等效LINQ实现 #

方式一：利用EF导航属性（推荐） #

借助模型中已定义的导航属性JobTitleLookup，EF会自动生成关联逻辑，无需手动编写Join：

// 根据EmployeeInfoId获取单个职位名称
public async Task<string?> GetJobTitle(int employeeInfoId)
{
    return await _ackContext.EmployeeInfos
        .Where(e => e.EmployeeInfoId == employeeInfoId)
        // 仅提取职位名称，避免加载完整实体，优化性能
        .Select(e => e.JobTitleLookup?.Title)
        .FirstOrDefaultAsync();
}

若需批量获取多个员工的职位名称：

public async Task<List<string?>> GetJobTitles(List<int> employeeInfoIds)
{
    return await _ackContext.EmployeeInfos
        .Where(e => employeeInfoIds.Contains(e.EmployeeInfoId))
        .Select(e => e.JobTitleLookup?.Title)
        .ToListAsync();
}

方式二：显式Join（与目标SQL逻辑完全匹配） #

如果希望完全复刻SQL的显式Join写法，可使用如下代码：

public async Task<string?> GetJobTitle(int employeeInfoId)
{
    return await _ackContext.EmployeeInfos
        .Join(
            _ackContext.JobTitleLookups,
            employee => employee.JobTitleLookupId,
            jobTitle => jobTitle.JobTitleLookupId,
            (employee, jobTitle) => new { employee.EmployeeInfoId, jobTitle.Title }
        )
        .Where(result => result.EmployeeInfoId == employeeInfoId)
        .Select(result => result.Title)
        .FirstOrDefaultAsync();
}

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