Why do I get the SpecialityName as null here?

Question

I am working with ASP.NET Core And Entity Framework, and when I request this controller I get this.

The problem in using the FromSql method is that it returns the SpecialityName as null, when the same query on SQL Server returns the actual value of SpecialityName.

This is what I do:

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); 
} 

This is part of the result:

{
    "id": 3,
    "firstName": "Hatem",
    "lastName": "Aiman",
    "email": "[email protected]",
    "specialityId": 1,
    "speciality": null
}

Here are my models and 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; }
}

Answer 1

The reason you were not getting the specialty is because you were trying to use an SQL statement to retrieve the specialty name, but using DbSet<Doctor>.FromSql() which is used to retrieve Doctor entities using customized SQL. Doctor has a reference to a Specialty entity, but using FromSql() it does not know to eager load the Specialty. 99.5% of the time you should never need to use FromSql. The same result would have been:

var doctors = _context.Doctors
    .ToList();

If you wanted the specialty entity:

var doctors = _context.Doctors
    .Include(x => x.Specialty)
    .ToList();

This would have populated the specialty entity with all properties contained as part of a Specialty entity.

When it comes to serializing such as with JSON results I recommend defining a DTO class separate to the Entities, or as your solution has done, using an anonymous type rather than returning entities. Entities represent data domain state and usually contain more information than the consumer needs. You also do not need to use explicit joins when you have navigation properties. This code:

var list = from doctor in _context.Doctors
    join speciality in _context.Specialities
        on doctor.SpecialityId equals speciality.SpecialityId
    select new
    {
        doctor.Id,
        doctor.FirstName,
        doctor.LastName,
        doctor.SpecialityId,
        doctor.Email,
        speciality.SpecialityName
    };

... can be replaced with:

var list = _context.Doctors
    .Select(d => new 
    {
        d.Id,
        d.FirstName,
        d.LastName,
        d.SpecialityId,
        d.Email,
        d.Specialty.SpecialityName // <- Access the related specialty navigation property.
    }).ToList();

Explicit joins should only be needed when you need to query across loosely coupled entities where navigation properties cannot be used due to the loose coupling not allowing for proper foreign keys. EF supports navigation properties to manage relationships and these should be used by default wherever possible. An example where using explicit joins would be used is for a de-normalized polymorphic relationship such as using an OwnerType and OwnerId. For example having an Address table that can relate to multiple tables defined by an OwnerType where the OwnerId loosely couples to the PK of that table. This is a somewhat sloppy database design which should be avoided in favour of separate specific Address tables for each parent, or use a many-to-many joining table to allow these relationships to be properly normalized. Still, where it is what you have to work with, explicit joins can be used.