ASP.NET Core中EF FromSql查询返回Speciality导航属性为null的问题

问题详情 #

使用FromSql执行关联查询时，返回的Doctor实体中speciality导航属性始终为null，但相同SQL在SQL Server中能查到正确的SpecialityName。

控制器代码 #

public IActionResult Index() 
{
    var list = _context.Doctors
                       .FromSql($"select Id, FirstName, LastName,Doctors.SpecialityId , Email , SpecialityName from Doctors join Specialities on Doctors.SpecialityId = Specialities.SpecialityId")
                       .ToList(); 
    return Json(list); 
} 

返回结果 #

{
    "id": 3,
    "firstName": "Hatem",
    "lastName": "Aiman",
    "email": "hatem@gmail.com",
    "specialityId": 1,
    "speciality": null
}

模型与DbContext定义 #

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }

    public DbSet<Doctor> Doctors { get; set; }
    public DbSet<Speciality> Specialities { get; set; }  
}

public class Doctor
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }

    public int SpecialityId { get; set; }
    public Speciality speciality { get; set; }
}

public class Speciality
{
    public int SpecialityId { get; set; }
    public string SpecialityName { get; set; }

    public ICollection<Doctor> Doctor { get; set; }
}

问题原因 #

FromSql仅负责将查询结果映射到Doctor实体的标量属性（如Id、SpecialityId），EF Core不会自动填充导航属性，也不会把查询中的SpecialityName字段映射到speciality对象里。导航属性的加载需要显式通过Include或LINQ关联查询处理。

解决方案 #

方案1：使用DTO投影（推荐） #

创建包含所需字段的DTO类，直接从SQL查询投影结果，避免依赖导航属性：

// 定义DTO
public class DoctorSpecialityDto
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public int SpecialityId { get; set; }
    public string SpecialityName { get; set; }
}

// 控制器查询
public IActionResult Index() 
{
    var sql = @"SELECT Id, FirstName, LastName, Doctors.SpecialityId, Email, SpecialityName
                FROM Doctors 
                JOIN Specialities ON Doctors.SpecialityId = Specialities.SpecialityId";
    
    var list = _context.Set<DoctorSpecialityDto>()
                       .FromSqlRaw(sql)
                       .ToList(); 
    return Json(list); 
}

方案2：用EF LINQ查询+Include加载导航属性 #

放弃手写SQL，改用EF Core的LINQ查询，通过Include显式加载关联的speciality：

public IActionResult Index() 
{
    var list = _context.Doctors
                       .Include(d => d.speciality) // 注意属性名与模型一致（小写speciality）
                       .ToList(); 
    return Json(list); 
}

如果需要过滤数据，直接在查询中添加Where条件即可，EF会自动生成对应的关联SQL。

方案3：手动填充导航属性（不推荐） #

若必须使用手写SQL，可查询后手动获取关联数据并填充：

public IActionResult Index() 
{
    // 查询医生基础数据
    var doctors = _context.Doctors
                       .FromSql($"SELECT Id, FirstName, LastName, Doctors.SpecialityId, Email FROM Doctors")
                       .ToList(); 
    
    // 批量获取关联的科室数据
    var specialityIds = doctors.Select(d => d.SpecialityId).Distinct().ToList();
    var specialities = _context.Specialities
                               .Where(s => specialityIds.Contains(s.SpecialityId))
                               .ToList();
    
    // 手动填充导航属性
    foreach (var doctor in doctors)
    {
        doctor.speciality = specialities.FirstOrDefault(s => s.SpecialityId == doctor.SpecialityId);
    }
    
    return Json(doctors); 
}

额外提示 #

• 模型中speciality属性为小写，JSON返回字段会与之保持一致；若需规范命名，可添加[JsonPropertyName("speciality")]特性或启用EF的约定命名规则。
• FromSqlRaw比FromSql更适合处理原生SQL字符串，若有参数请使用参数化查询避免注入风险。

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