Create a nested list of items from DataTable.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

namespace WebApi_Entity_Framework_Sqlonection.Models

{

    public class Record

    {

      public string Policy_id { get; set; }

      public string Policy_name { get; set; }

      public List<SubRecord> SubRec { get; set; }

    }

    public class SubRecord

    {

        public string policy_det_id { get; set; }

        public string Main_policy_name { get; set; }

        public string policy_status { get; set; }

        public string Policy_Mode { get; set; }

        public string file_path { get; set; }

    }

}

--------------------------------------------------------------------------------------------------------------------

        [HttpGet]

        public List<Record> GetXelSheet()

        {

            string path1 = "C:\\Users\\........\\Desktop\\Book1.xlsx";

            string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

            DataTable dt = Models.ImportXeslSheeet.ConvertXSLXtoDataTable(path1, connString);

            List<Record> tankReadings = new List<Record>();

            foreach (DataRow row in dt.Rows)

            {

                if (tankReadings.Count == 0)

                {

                    tankReadings.Add(new Record()

                    {

                        Policy_id = row["Policy_id"].ToString(),

                        Policy_name = row["Policy_name"].ToString(),

                    });

                }

                else

                {

                   if (!tankReadings.Exists(w => w.Policy_id ==                             row["Policy_id"].ToString()))

                    {

                        tankReadings.Add(new Record()

                        {

                            Policy_id = row["Policy_id"].ToString(),

                            Policy_name = row["Policy_name"].ToString(),

                        });

                    }

                }

            }

            for (int i = 0; i < tankReadings.Count; i++)

            {

                List<SubRecord> _SubRec = new List<SubRecord>();

                foreach (DataRow row in dt.Rows)

                {

                    if (tankReadings[i].Policy_id == row["Policy_id"].ToString())

                    {

                        _SubRec.Add(new SubRecord()

                        {

                            file_path = "", 

                            Main_policy_name = row["Main_policy_name"].ToString(),                                        policy_det_id = "",

                            Policy_Mode = "", 

                            policy_status = ""

                        });

                    }

                }

                tankReadings[i].SubRec = _SubRec;

            }

            return tankReadings;

        }

    }