Complex GROUP BY on DataTable

Question

I have a complex entity CostPageDTOas 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 listed below: but I am 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 DataTable

REFERENCES

How do you convert a DataTable into a generic list?

you also should consider using a `ConcurrentBag`. `List` isn't thread safe. — Daniel A. White, Dec 09 '13 at 13:50
Why not change the DAL function to use Linq2SQL or entity framework and it would be able to return a List directly instead of a DataTable. Also List is not thread safe so you can use it like that. — Magnus, Dec 09 '13 at 13:51
@DanielA.White I am getting the datatable from a SqlDataReader. `SqlDataReader reader = command.ExecuteReader(); table.Load(reader);` — LCJ, Dec 09 '13 at 13:51
@DanielA.White To make the processing faster... If it is a wrong thought, I am fine with a non-parallel approach also. — LCJ, Dec 09 '13 at 14:55
@Lijo An operation such as this is going to be bottlenecked at the IO, not the processing. There is likely no reason to parallelize something like this. Odds are pretty high that not only are you making your program much more complex, you're actually making it slower. On top of that, others have already mentioned that you aren't synchronizing properly, so this won't even (necessarily) produce the correct result. — Servy, Dec 09 '13 at 15:15
@Lijo Just use a `foreach` rather than a `parallel.ForEach`... — Servy, Dec 09 '13 at 15:34
@Servy. Since I am not clear on the statement `bottlenecked at the IO, not the processing` could you please post an answer elaborating this. — LCJ, Dec 09 '13 at 16:18
@Lijo The network request that you're making to the DB to get the data is going to be many orders of magnitude slower than the times spent processing the result. When this processing makes up .001% of the execution time, cutting that time in half, or more, has effectively no impact on the execution time of your application. — Servy, Dec 09 '13 at 16:20

score 1 · Accepted Answer · edited Dec 09 '13 at 21:07

I'm assuming your database query returns a join between CostPage and Item. If that's the case, first you have to group your rows to get the values for CostPage, after that project to your DTO type. I really doubt that you'll see much benefit in parallelizing the code.

Your code should look roughly 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();

I've a similar problem with dynamical grouping using LINQ. Can you please have a look [here](http://stackoverflow.com/questions/22694680/how-to-use-linq-for-conditional-grouping-and-filtering-on-a-datatable)? — Cheshire Cat, Apr 01 '14 at 10:54

Complex GROUP BY on DataTable

1 Answers1

Linked