In the below context I have joined two table based on some criteria. Now I need to calculate total value from the result of that joined table.

[HttpGet("inner-join/{id}")]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public IActionResult GetReport(DateTime id)
{
  try
  {
    IEnumerable<BTBPending> objBTBPendingList = _unitOfWork.BTBPending.GetAll(includeProperties: "ProformaInvoice,ContractList,SupplierList,CountryList,ItemList,BuyerList,StyleList,TradeTermList,ErpRemarksList,StatusList,LcNoList,UdAmendList");

    IEnumerable<ProformaInvoice> objProformaInvoiceList = _unitOfWork.ProformaInvoice.GetAll(includeProperties: "ActualContract,ContractList,SupplierList,CountryList,ItemList,BuyerList,StyleList,TradeTermList");


    var query = objBTBPendingList
        .Where(x => x.LcOpenDate.Value.Year == id.Year && x.LcOpenDate.Value.Month == id.Month)
        .Where(x => x.CountryListId == 26)
        .Where(x => x.StatusListId == 12 || x.StatusListId == 13 || x.StatusListId == 14)
        .Join(objProformaInvoiceList,
              btbPending => btbPending.ContractListId,
              pi => pi.ContractListId, 
              (btbPending, pi) => new
              {
                LcNo = btbPending.LcNoList,
                Value = btbPending.PiValue,

                ContractNo = pi.ContractList,
                Buyer = pi.BuyerList,
                PiNo = pi.PINo,
                Supplier = pi.SupplierList,
                Item = pi.ItemList
              }).ToList();


     return Ok(query);
   }
 catch (Exception ex)
   {
     return StatusCode(500, "Internal Server Error, Please Try Again Leter!");
   }
}

After joining the these two table I need to return the sum from the below Value = btbPending.PiValue Column which I got after joining the tables.

.Join(objProformaInvoiceList,
      btbPending => btbPending.ContractListId,
      pi => pi.ContractListId, 
      (btbPending, pi) => new
      {
      LcNo = btbPending.LcNoList,
      Value = btbPending.PiValue,

      ContractNo = pi.ContractList,
      Buyer = pi.BuyerList,
      PiNo = pi.PINo,
      Supplier = pi.SupplierList,
      Item = pi.ItemList
      }).ToList();

CodePudding user response：

You can calculate the sum like this var sum = query.Select(c => c.Value).Sum();