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)

CodePudding user response：

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