GROUP BY Complex by DataTable

Question

I have a complex CostPageDTO object as shown below:

 public class CostPageDTO { public string CostPageNumber { get; set; } public string Description { get; set; } public char OrderType { get; set; } public string VendorName { get; set; } public List<ItemDTO> Items { get; set; } } public class ItemDTO { public string BrandCode { get; set; } public string ItemDescription { get; set; } public string ItemID { get; set; } } 

We need to create a List<CostPageDTO> from a datatable . I started as shown below: but I'm not sure how to apply the GROUP BY clause here to create a List<ItemDTO> inside CostPageDTO.

 DataTable table = new DataTable(); SqlDataReader reader = command.ExecuteReader(); table.Load(reader); reader.Close(); List<CostPageDTO> costPages = new List<CostPageDTO>(); Parallel.ForEach(table.AsEnumerable(), (dr) => { costPages.Add(new CostPageDTO() { CostPageNumber = dr[0].ToString(), Description = dr[1].ToString(), OrderType = Convert.ToChar(dr[2].ToString()), VendorName = dr[3].ToString() }); }); 

• How can we create the required list from a DataTable

LITERATURE

• How to convert a DataTable to a general list?

Answer 1

I assume that your database query returns a connection between CostPage and Item. If so, first you need to group your lines to get the values ​​for CostPage, after this project for your DTO type. I really doubt that you will see many benefits when parallelizing code.

Your code should look something like this:

 costPages = table.AsEnumerable().GroupBy(dr=> new { CostPageNumber = dr[0].ToString(), Description = dr[1].ToString(), OrderType = Convert.ToChar(dr[2].ToString()), VendorName = dr[3].ToString() }) .Select(x => new CostPageDTO(){ CostPageNumber = x.Key.CostPageNumber, Description = x.Key.Description, OrderType = x.Key.OrderType, VendorName = x.Key.VendorName, Items = x.Select(dr=> new ItemDTO{ //ItemDTO mapping goes here ItemID=dr[Constants.SearchPage.ITMID].ToString() }).ToList() }).ToList();