More elegant way to group DataTable data in structs in C# (using Linq?)

Question

I have groups of data stored in a database table that I want to transform into a list of group-struct, each containing arrays of data.

I am able to do this using a rather long method. I was wondering if there is a more compact way of achieving this? I suspect Linq is supposed to be perfect for this kind of operation but I really don't know how to start.

The following example illustrates what I am currently doing. My real data is more complex.

The data is to be stored in a struct like this

public struct GroupData
{
    private string aString;
    private int anInt;

    public GroupData(string aString, int anInt)
    {
        this.aString = aString;
        this.anInt = anInt;
    }
}

Which again is to be stored in a Group-struct

public struct Group
{
    private string groupId;
    private GroupData[] groupData;

    public Group(string groupId, GroupData[] groupData)
    {
       this.groupId = groupId;
       this.groupData = groupData;
    }
}

I am currently doing this

//Create some dummy data
DataTable table = new DataTable();
table.Columns.Add("GROUP_ID", typeof(string));
table.Columns.Add("A_STRING", typeof(string));
table.Columns.Add("AN_INT", typeof(int));

table.Rows.Add("A", "this_is_A2", 7);
table.Rows.Add("A", "this_is_A2", 4);
table.Rows.Add("B", "this_is_B1", 3);
table.Rows.Add("C", "this_is_C1", 1);
table.Rows.Add("D", "this_is_D1", 3);
table.Rows.Add("D", "this_is_D2", 2);

//Create list of groups with arrays of groupdata
string theString, theGroupId;
int theInt;
List<Group> theGroups = new List<Group>();
List<GroupData> groupDataList;
Dictionary<string, List<GroupData>> groupDataDict = new Dictionary<string, List<GroupData>>();

//Read all rows and convert to structs
for (int i = 0; i < table.Rows.Count; i++)
{
    theGroupId = (string)table.Rows[i]["GROUP_ID"];
    theString = (string)table.Rows[i]["A_STRING"];
    theInt = (int)table.Rows[i]["AN_INT"];

    //Collect all GroupData into their respective Groups
    if (!groupDataDict.TryGetValue(theGroupId, out groupDataList))
    {
        groupDataList = new List<GroupData>();
        groupDataDict.Add(theGroupId, groupDataList);
    }
    groupDataList.Add(new GroupData(theString, theInt));
}

//Insert each group into the list
foreach (KeyValuePair<string, List<GroupData>> kv in groupDataDict)
    theGroups.Add(new Group(kv.Key, kv.Value.ToArray()));

I see that my question is closely related to this post Group by in LINQ and I guess I could first transform my datatable to a list, and then use the prescribed method. But Ideally I would like to bypass the step of first transforming to a list and operate directly on the DataTable.

Answer 1

While I would highly recommend switching from DataTable to using models as most modern applications do (see references below), Microsoft has provided an AsEnumerable() extension method in System.Data.DataTableExtensions that is all you need to use LINQ on a DataTable.

//Create some dummy data
DataTable table = new DataTable();
table.Columns.Add("GROUP_ID", typeof(string));
table.Columns.Add("A_STRING", typeof(string));
table.Columns.Add("AN_INT", typeof(int));

table.Rows.Add("A", "this_is_A2", 7);
table.Rows.Add("A", "this_is_A2", 4);
table.Rows.Add("B", "this_is_B1", 3);
table.Rows.Add("C", "this_is_C1", 1);
table.Rows.Add("D", "this_is_D1", 3);
table.Rows.Add("D", "this_is_D2", 2);

var groups = (from dt in table.AsEnumerable()
              group dt by dt.Field<string>("GROUP_ID") into g
              select new { 
                  GroupID = g.Key, 
                  GroupData = g.Select(i => i.Field<int>("AN_INT")) }
              ).ToList();

Reference: LINQ query on a DataTable

A few articles you should read before you decide to use DataTable in the 2020's (keep in mind it is nearly two decades old and is not type safe):

• Reasons to Move from DataTables to Generic Collections
• What are the advantages of using POCOs over DataTables?