Group by on DataTable

Question

DataTable1

DataTable2

What I'm looking for is:

Step 1- From DataTable1, get columnName where colGroup is YES or OK.

Step 2- Query DataTable2, and group by on columns returned from DataTable1 (Step1)

Answer 1

This is tricky, but can be done with the dynamic Linq library. using System.Linq.Dynamic and using Newtonsoft.Json.

void Main()
{
    var dataTable1 = new[] { 
        new DataTable1 { columnName = "OriginCompany", colGroup = "YES" },
        new DataTable1 { columnName = "OriginAddress", colGroup = "YES" },
        new DataTable1 { columnName = "OriginZip", colGroup = "OK" },
        new DataTable1 { columnName = "OriginState", colGroup = "NO" }};

    var dataTable2 = new[] {
        new DataTable2 { OriginCompany = "Company1", OriginAddress = "PAdds2_1", OriginZip = "10001", OriginState = "MI" }, 
        new DataTable2 { OriginCompany = "Company1", OriginAddress = "PAdds2_1", OriginZip = "10001", OriginState = "MI" },
        new DataTable2 { OriginCompany = "Company2", OriginAddress = "PAdds2_2", OriginZip = "10002", OriginState = "AZ" },
        new DataTable2 { OriginCompany = "Company3", OriginAddress = "PAdds2_3", OriginZip = "10003", OriginState = "MI" },
        new DataTable2 { OriginCompany = "Company3", OriginAddress = "PAdds2_3", OriginZip = "10003", OriginState = "UT" }};

    // Select the columns that will form the group-by
    var groupByColumns = dataTable1
        .Where(g => g.colGroup == "YES" || g.colGroup == "OK")
        .Select(g => g.columnName);

    // Use the dynamic linq GroupBy function
    var groupedData = dataTable2
        .GroupBy($"new({string.Join(",", groupByColumns)})", "it")
        .Select("new(Key as TheKey, it as MatchedRows)");
    
    foreach (var grp in groupedData)
    {
        // Using dynamic objects is not very fun, but the Newtonsoft Json 
        // library makes it bearable. Maybe there's a better way?
        var key = ((dynamic)grp).TheKey;
        Console.WriteLine($"Group: {JsonConvert.SerializeObject(key)}");
        
        // Again using Newtonsoft to convert dynamic objects to typed objects
        var rows = ((dynamic)grp).MatchedRows;
        var matches = (List<DataTable2>)JsonConvert.DeserializeObject<List<DataTable2>>(JsonConvert.SerializeObject(rows));
        foreach (var match in matches)
        {
            Console.WriteLine($"---> Value: {match}");
        }       
    }
}

public class DataTable1
{
    public string columnName { get; set; }
    public string colGroup  { get; set; }
}

public class DataTable2
{
    public string OriginCompany { get; set; }
    public string OriginAddress { get; set; }
    public string OriginZip { get; set; }
    public string OriginState { get; set; }
    public override string ToString() { return $"OriginCompany:{OriginCompany}, OriginAddress:{OriginAddress}, OriginZip:{OriginZip}, OriginState:{OriginState}"; }
}

This will return the following, which I believe is your desired output:

Group: {"OriginCompany":"Company1","OriginAddress":"PAdds2_1","OriginZip":"10001"}
---> Value: OriginCompany:Company1, OriginAddress:PAdds2_1, OriginZip:10001, OriginState:MI
---> Value: OriginCompany:Company1, OriginAddress:PAdds2_1, OriginZip:10001, OriginState:MI
Group: {"OriginCompany":"Company2","OriginAddress":"PAdds2_2","OriginZip":"10002"}
---> Value: OriginCompany:Company2, OriginAddress:PAdds2_2, OriginZip:10002, OriginState:AZ
Group: {"OriginCompany":"Company3","OriginAddress":"PAdds2_3","OriginZip":"10003"}
---> Value: OriginCompany:Company3, OriginAddress:PAdds2_3, OriginZip:10003, OriginState:MI
---> Value: OriginCompany:Company3, OriginAddress:PAdds2_3, OriginZip:10003, OriginState:UT