C#/webAPI/将sql多个查询结果集转成嵌套的对象

数据库的查询结果:

 

 

 C# 实体模型

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Xml.Serialization;

namespace XXX
{
    /// <summary>
    /// 费用详情
    /// </summary>
    public class FeeDetailsViewModel
    {

        ///<summary>
        ///总费用
        ///</summary>
        public decimal ItemTotalFee { get; set; }


        ///<summary>
        ///缴费详情信息
        ///</summary>
        public List<PaymentDetails> Products { get; set; }
    }

    /// <summary>
    /// 缴费详情信
    /// </summary>
    public class PaymentDetails {
        ///<summary>
        ///科室总费用2
        ///</summary>
        public decimal ItemTotalFee { get; set; }


        ///<summary>
        ///执行科室编号
        ///</summary>
        public string OperateDeptCode { get; set; }


        ///<summary>
        ///执行科室名称
        ///</summary>
        public string OperateDeptName { get; set; }


        ///<summary>
        ///项目类容
        ///</summary>
        public List<ItemLists> ItemList { get; set; }

    }
    /// <summary>
    /// 项目类容
    /// </summary>
    public class ItemLists {
        ///<summary>
        ///账单详情
        ///</summary>
        public string BillDetail { get; set; }


        ///<summary>
        ///单价
        ///</summary>
        public decimal UnitPrice { get; set; }


        ///<summary>
        ///数量
        ///</summary>
        public decimal Amount { get; set; }


        ///<summary>
        ///金额
        ///</summary>
        public  decimal ItemFee { get; set; } 
    }
}

　　处理实体嵌套

            
            var response = await _context.MultipleResults("pLF_MZZF02", parameters)
               .With<FeeDetailsViewModel>() //sql 第一个查询结果集
               .With<PaymentDetails>()      //sql 第二个查询结果集
               .With<ItemLists>()           //sql 第三个查询结果集
               .Execute();
            //检查响应代码
            List<FeeDetailsViewModel> FeeDetailsViewModelResoult = new List<FeeDetailsViewModel>();
            List<FeeDetailsViewModel> _FeeDetailsViewModelList = (List<FeeDetailsViewModel>)response[0];
            List<PaymentDetails> _PaymentDetailsList = (List<PaymentDetails>)response[1];
            List<ItemLists> _ItemListsList = (List<ItemLists>)response[2];
            for (int i = 0; i < _FeeDetailsViewModelList.Count(); i++)
            {
                FeeDetailsViewModel _FeeDetailsViewModel = new FeeDetailsViewModel();
                _FeeDetailsViewModel.ItemTotalFee = _FeeDetailsViewModelList[i].ItemTotalFee;
                _FeeDetailsViewModel.Products = new List<PaymentDetails>();
                for (int j = 0; j < _PaymentDetailsList.Count(); j++)
                {
                    PaymentDetails _PaymentDetails = new PaymentDetails();
                    _PaymentDetails.ItemTotalFee = _PaymentDetailsList[j].ItemTotalFee;
                    _PaymentDetails.OperateDeptCode = _PaymentDetailsList[j].OperateDeptCode;
                    _PaymentDetails.OperateDeptName = _PaymentDetailsList[j].OperateDeptName;
                    _PaymentDetails.ItemList = new List<ItemLists>();
                    for (int k = 0; k < _ItemListsList.Count(); k++)
                    {
                        ItemLists _ItemLists = new ItemLists();
                        _ItemLists.BillDetail = _ItemListsList[k].BillDetail;
                        _ItemLists.UnitPrice = _ItemListsList[k].UnitPrice;
                        _ItemLists.Amount = _ItemListsList[k].Amount;
                        _ItemLists.ItemFee = _ItemListsList[k].ItemFee;
                        _PaymentDetails.ItemList.Add(_ItemLists);
                    }
                    _FeeDetailsViewModel.Products.Add(_PaymentDetails);
                }
                FeeDetailsViewModelResoult.Add(_FeeDetailsViewModel);
            }
            return new BaseViewModel
            {
                Code = Convert.ToInt32(parameters.SingleOrDefault(c => c.ParameterName == "Code")?.Value),
                Message = parameters.SingleOrDefault(c => c.ParameterName == "Msg")?.Value.ToString(),
                Data = FeeDetailsViewModelResoult
            };

　　最终得到的json结果

{
  "code": 1,
  "message": "",
  "body": [
    {
      "ItemTotalFee": 4.07,
      "Products": [
        {
          "ItemTotalFee": 4.07,
          "OperateDeptCode": "0",
          "ItemList": [
            {
              "BillDetail": "",
              "UnitPrice": 4.073,
              "Amount": 1,
              "ItemFee": 0
            }
          ]
        }
      ]
    }
  ]
}