How to query a table and map all the related tables using FromSqlRaw() in EF Core?

Alex Wright 2022

I want to write a query for the API to get all desired data as JSON while keeping all related tables in the final result. I have the following model: Equipment.cs

public class Equipment
{
[Key]
public long EquipmentId { get; set; }
[Required]
public string EquipmentCode { get; set; }
[Required]
public string EquipmentTitle { get; set; }
[Required]
public bool IsActive { get; set; }

//Navigation properties
\[Required\]
public int CostCenterId { get; set; }
public CostCenter CostCenter { get; set; }
public int? EquipmentCategoryId { get; set; }
public EquipmentCategory? EquipmentCategory { get; set; }
public int EquipmentTypeId { get; set; }
public EquipmentType equipmentType { get; set; }

}

CostCenter.cs

public class CostCenter
{
[Key]
public int CostCenterId { get; set; }
[Required]
[MaxLength(100)]
public string Title { get; set; }
[Required]
public bool IsActive { get; set; } = true;

//Navigation properties
\[JsonIgnore\]
public virtual List? Equipments { get; set; }

}

EquipmentCategory.cs

public class EquipmentCategory
{
[Key]
public int EquipmentCategoryId { get; set; }
[Required]
[MaxLength(100)]
public string CategoryCode { get; set; }
[Required]
[MaxLength(100)]
public string CategoryName { get; set; }

//Navigation property
\[JsonIgnore\]
public virtual List? Equipments { get; set; }

}

EquipmentType.cs

public class EquipmentType
{
[Key]
public int EquipmentTypeId { get; set; }
[Required]
[MaxLength(100)]
public string EquipmentTypeTitle { get; set; }

//Navigation property
\[JsonIgnore\]
public virtual List? Equipments { get; set; }

}

I use the following codes in the controller:

var query = "SELECT * FROM Equipments WHERE EquipmentCode LIKE '1101%'"

var result = _sqlServerContext.Equipments.FromSqlRaw(query);

return Ok(result);