How to query a table and map all the related tables using Dapper?-CodePudding

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 studied this thread but I could not implement it in my case. 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<Equipment>? 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<Equipment>? 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<Equipment>? Equipments { get; set; }
}

I use the following codes in the controller:

var query = "SELECT * FROM Equipments INNER JOIN CostCenters ON CostCenters.CostCenterId = Equipments.CostCenterId  WHERE  EquipmentCode LIKE '1101%'"

var result = connection.Query<Equipment>(query).ToList();

return Ok(result);

CodePudding user response：

First you need to join all tables and then map the relations to Equipment object like this:

var query = @"
SELECT * FROM Equipments INNER JOIN CostCenters ON CostCenters.CostCenterId = Equipments.CostCenterId  
INNER JOIN EquipmentCategories ec ON Equipments.EquipmentCategoryId = ec.EquipmentCategoryId 
INNER JOIN EquipmentTypes t ON Equipments.EquipmentTypeId = t.EquipmentTypeId
WHERE  EquipmentCode LIKE '1101%'";

var result = connection.Query<Equipment,CostCenter,EquipmentCategory,EquipmentType, Equipment>
(query,(equipment, center, category, type)=>{

equipment.CostCenter=center;
equipment.EquipmentCategory=category;
equipment.EquipmentType=type;

return equipment;
}).ToList();

return Ok(result);