return json data from 2 sql querys into the same array-CodePudding

I have a method where I use 2 sql querys to get data. I select data from both of the querys based on the same "claimId", so as a result they both always provide the same number of rows. And at the moment the data being returned are in 2 different arrays. Now, i would like to change the output so that they are being returned as one instead (i will explain with code later on). So, here's the method:

    public async Task<TireList> getAllTireClaims(Tires model)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@claimId", model.claimId);
        var getAllTires = await _sqlconnection.QueryAsync<Tires>($@"            
        SELECT
        Tire.ID as tireId, Claim.ID as claimId,
        RequestType.Name as serviceReason,
        Quantity, PricePerUnit, Tire.Price as price,
        RequestTireTypes.Name as tireTypeId, 
        Brand, Model, RequestTireWidth.Name as widthID,
        RequestTireHeight.Name as heightID, RequestTireDiameter.Name as diameterID
        FROM Claim
        INNER JOIN RequestType ON Claim.RequestTypeID = RequestType.ID
        INNER JOIN ClaimCrossTire ON Claim.ID = ClaimCrossTire.ClaimID
        INNER JOIN Tire ON ClaimCrossTire.TireID = Tire.ID
        INNER JOIN RequestTireTypes ON Tire.TireTypeID = RequestTireTypes.ID
        INNER JOIN RequestTireWidth ON Tire.WidthID = RequestTireWidth.ID
        INNER JOIN RequestTireHeight ON Tire.HeightID = RequestTireHeight.ID
        INNER JOIN RequestTireDiameter ON Tire.DiameterID = RequestTireDiameter.ID
        WHERE ClaimCrossTire.ClaimID = @claimId AND RequestTypeID = 3 ", parameters);

        var getTireService = await _sqlconnection.QueryAsync<TireServiceInfo>($@"SELECT TireServiceID
        FROM CostItem cI
        JOIN ClaimCrossCostItem ccrossI ON cI.ID = ccrossI.CostItemID
        JOIN Claim c  ON ccrossI.ClaimID = c.ID
        WHERE (TireServiceID = 3 OR TireServiceID = 5)
        AND c.ID = @claimId", parameters);

        return new TireList()
        {
            tireservice = getTireService,
            tires = getAllTires
        };
    }

The models:

public class Tires
{
    public int claimId { get; set; } = 0;
    public int tireId { get; set; } = 0;
    public string serviceReason { get; set; } = "";
    public string TireService { get; set; } = "";
    public int quantity { get; set; } = 0;
    public string brand { get; set; } = "";
    public string model { get; set; } = "";
    public int pricePerUnit { get; set; } = 0;
    public int price { get; set; } = 0;
    public string tireTypeId { get; set; } = "";
    public string widthID { get; set; } = "";
    public string heightID { get; set; } = "";
    public string diameterID { get; set; } = "";
}

public class TireList
{
    public IEnumerable<Tires> tires { get; set; }
    public IEnumerable<TireServiceInfo> tireservice { get; set; }
}

public class TireServiceInfo
{
    public string TireServiceId { get; set; } = "";
}

And the output:

{
   "tires":[
      {
         "claimId":1510837,
         "tireId":149,
         "serviceReason":"Tires",
         "tireService":"",
         "quantity":2,
         "brand":"brand",
         "model":"model",
         "pricePerUnit":1,
         "price":6,
         "tireTypeId":"Summer Tires",
         "widthID":"145",
         "heightID":"25",
         "diameterID":"14"
      },
      {
         "claimId":1510837,
         "tireId":150,
         "serviceReason":"Tires",
         "tireService":"",
         "quantity":22,
         "brand":"brand2",
         "model":"Model2",
         "pricePerUnit":3,
         "price":66,
         "tireTypeId":"Winter tires unstudded",
         "widthID":"165",
         "heightID":"35",
         "diameterID":"16"
      }
   ],
   "tireservice":[
      {
         "tireServiceId":"3"
      },
      {
         "tireServiceId":"3"
      }
   ]
}

I would like to have the output instead be something like:

{
   "tires":[
      {
         "claimId":1510837,
         "tireId":149,
         "serviceReason":"Tires",
         "tireService":"",
         "quantity":2,
         "brand":"brand",
         "model":"model",
         "pricePerUnit":1,
         "price":6,
         "tireTypeId":"Summer Tires",
         "widthID":"145",
         "heightID":"25",
         "diameterID":"14",
         "tireservice":[
            {
               "tireServiceId":"3"
            }
         ]
      },
      {
         "claimId":1510837,
         "tireId":150,
         "serviceReason":"Tires",
         "tireService":"",
         "quantity":22,
         "brand":"brand2",
         "model":"Model2",
         "pricePerUnit":3,
         "price":66,
         "tireTypeId":"Winter tires unstudded",
         "widthID":"165",
         "heightID":"35",
         "diameterID":"16",
         "tireservice":[
            {
               "tireServiceId":"3"
            }
         ]
      }

So that tireserviceId instead is being inside of the tires array. Any ideas on how to make this possible?

CodePudding user response：

Your Tires class needs the tireservice property to be present in json.

public class Tires
{
    public int claimId { get; set; } = 0;
    public int tireId { get; set; } = 0;
    public string serviceReason { get; set; } = "";
    public string TireService { get; set; } = "";
    public int quantity { get; set; } = 0;
    public string brand { get; set; } = "";
    public string model { get; set; } = "";
    public int pricePerUnit { get; set; } = 0;
    public int price { get; set; } = 0;
    public string tireTypeId { get; set; } = "";
    public string widthID { get; set; } = "";
    public string heightID { get; set; } = "";
    public string diameterID { get; set; } = "";
    public IEnumerable<TireServiceInfo> tireservice { get; set; }
}

public class TireList
{
    public IEnumerable<Tires> tires { get; set; }
    
}

Add the ClaimId to your TireServiceInfo

public class TireServiceInfo
{
    public string TireServiceId { get; set; } = "";
    public string ClaimId { get; set; } = "";
}

and the Function should look like:

   public async Task<TireList> getAllTireClaims(Tires model)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@claimId", model.claimId);
        var getAllTires = await _sqlconnection.QueryAsync<Tires>($@"            
        SELECT
        Tire.ID as tireId, Claim.ID as claimId,
        RequestType.Name as serviceReason,
        Quantity, PricePerUnit, Tire.Price as price,
        RequestTireTypes.Name as tireTypeId, 
        Brand, Model, RequestTireWidth.Name as widthID,
        RequestTireHeight.Name as heightID, RequestTireDiameter.Name as diameterID
        FROM Claim
        INNER JOIN RequestType ON Claim.RequestTypeID = RequestType.ID
        INNER JOIN ClaimCrossTire ON Claim.ID = ClaimCrossTire.ClaimID
        INNER JOIN Tire ON ClaimCrossTire.TireID = Tire.ID
        INNER JOIN RequestTireTypes ON Tire.TireTypeID = RequestTireTypes.ID
        INNER JOIN RequestTireWidth ON Tire.WidthID = RequestTireWidth.ID
        INNER JOIN RequestTireHeight ON Tire.HeightID = RequestTireHeight.ID
        INNER JOIN RequestTireDiameter ON Tire.DiameterID = RequestTireDiameter.ID
        WHERE ClaimCrossTire.ClaimID = @claimId AND RequestTypeID = 3 ", parameters);

//add ClaimId to your SELECT statement
        var getTireService = await _sqlconnection.QueryAsync<TireServiceInfo>($@"SELECT TireServiceID, @claimId as ClaimId
        FROM CostItem cI
        JOIN ClaimCrossCostItem ccrossI ON cI.ID = ccrossI.CostItemID
        JOIN Claim c  ON ccrossI.ClaimID = c.ID
        WHERE (TireServiceID = 3 OR TireServiceID = 5)
        AND c.ID = @claimId", parameters);

var service =   getTireService;
var ret =  new TireList()
            {                
                tires = getAllTires

            };
foreach (var t in ret.tires)
{
 t.tireservice = service.Where (s => s.ClaimId.Equals(t.ClaimId));
}
return ret;
}

Couldn't test w/o the database, and there might be typos :-)