I am working with ASP.NET Core And Entity Framework And when I request this Controller I get this error.

public  IActionResult Index()
{
    var list = _context.Doctors.Include(a => a.speciality).ToList();
    return Json(list);
}

this is the controller code and here is 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; }
}

I tried using FROMSQL but it gave me a lot of errors, I tried the same query on SQL Server and it works fine

select Id, FirstName, LastName, Doctors.SpecialityId, Email, SpecialityName
from Doctors
join Specialities on Doctors.SpecialityId = Specialities.SpecialityId 

This query works on SQL Server, I had to type each column because in my code if I typed specific columns it give me an error. I figured out it works when I type all columns, but this query in my code does not display the SpecialityName and its displayed as null.

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

It displays the Speciality Name as null but in SQL Server it works okay.

If I didn’t type all columns in the query I get errors like this.

InvalidOperationException: The required column ‘FirstName’ was not present in the results of a ‘FromSql’ operation.

The problem in using FROMSQL method is that it gives me the SpecialityName as null. Whereas the same query on SQL Server gives me the SpecialityName.

This is part of the result of FROMSQL

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

And when I use this method I get another error

public  IActionResult Index()
{
    var list = _context.Doctors.Include(a => a.speciality ). ToList();
    return Json(list);
}

JsonException: A possible object cycle was detected. This can either be due to a cycle or if the object depth is larger than the maximum allowed depth of 32. Consider using ReferenceHandler.Preserve on JsonSerializerOptions to support cycles. Path: $.speciality.Doctor.speciality.Doctor.speciality.Doctor.speciality.Doctor.speciality.Doctor.speciality.Doctor.speciality.Doctor.speciality.Doctor.speciality.Doctor.speciality.Doctor.Id.