Linq groupby on datatables

Question

DataTable1

columnName          colGroup
    -----------------   ---------------
    OriginCompany       YES
    OriginAddress       YES
    OriginZip               OK
    OriginState             NO

DataTable2

OriginCompany   OriginAddress       OriginZip       OriginState
------------           ------------         ------------    ------------      
Company1        PAdd2_1         10001           MI
Company1        PAdd2_1         10001           MI  

Company2        PAdd2_2         10002           MI

Company3        PAdd2_3         10003           MI
Company3        PAdd2_3         10003           MI

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

@Zeeshan Dar , Based on your description, you want to get a group result by the condition of the step1.

According to my attempt, I suggest that you could use dynamic linq to Groupby a string array.(Need to install nuget-package System.Linq.Dynamic)

Here is a code example you could refer to.

using System.Linq.Dynamic;  
    static void Main(string[] args)  
            {  
                DataTable table1=new DataTable();  
                table1.Columns.Add("columnName", typeof(string));  
                table1.Columns.Add("colGroup", typeof(string));  
                table1.Rows.Add("OriginCompany", "Yes");  
                table1.Rows.Add("OriginAddress", "Yes");  
                table1.Rows.Add("OriginZip", "OK");  
                table1.Rows.Add("OriginState", "NO");  
                var result = table1.AsEnumerable().Where(i => i.Field<string>("colGroup")=="Yes"||i.Field<string>("colGroup")=="Ok").Select(i => i.Field<string>("columnName")).ToList();  
      
                DataTable table2 = new DataTable();  
                foreach (DataRow  item in table1.Rows)  
                {  
                    table2.Columns.Add(item[0].ToString());  
                }  
                table2.Rows.Add("Company1", "PAdd2_1", 1001, "MI");  
                table2.Rows.Add("Company1", "PAdd2_1", 1001, "MI");  
                table2.Rows.Add("Company2", "PAdd2_2", 1002, "MI");  
                table2.Rows.Add("Company3", "PAdd2_3", 1003, "MI");  
                table2.Rows.Add("Company3", "PAdd2_3", 1003, "MI");  
                table2.Rows.Add("Company1", "PAdd2_1", 10019, "MI");  
                string field = string.Format("new({0})", string.Join(",", result));  
                var list1 = (from DataRow dr in table2.Rows  
                               select new   
                               {  
                                   OriginCompany =  dr["OriginCompany"].ToString(),  
                                   OriginAddress = dr["OriginAddress"].ToString(),  
                                   OriginZip =Convert.ToInt32(dr["OriginZip"].ToString()),  
                                   OriginState = dr["OriginState"].ToString()  
                               }).ToList();  
                var result1 = list1.GroupBy(field, "it");  
                foreach (IGrouping<dynamic, dynamic> item in result1)  
                {  
                    foreach (var i in item)  
                    {  
                        Console.WriteLine(i.OriginCompany);  
                        Console.WriteLine(i.OriginAddress);  
                        Console.WriteLine(i.OriginZip);  
                        Console.WriteLine(i.OriginState);  
                    }  
                    Console.WriteLine("********");  
                }  
      
      
                Console.ReadKey();  
      
      
            }  

Result:(I add another test data to test the code)

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Seems you need to make the second column uniform from the values given e.g.

Note you can always clone the first table than populate without alias column.

public static void Example()
{
    DataTable table = new DataTable();
    table.Columns.Add("ColumnName", typeof(string));
    table.Columns.Add("ColumnGroup", typeof(string));
    table.Columns.Add("Alias", typeof(string));

    table.Rows.Add("A", "YES");
    table.Rows.Add("B", "YES");
    table.Rows.Add("C", "OK");
    table.Rows.Add("D", "NO");

    for (int index = 0; index < table.Rows.Count; index++)
    {
        if (table.Rows[index].Field<string>("ColumnGroup") == "YES" || 
            table.Rows[index].Field<string>("ColumnGroup") == "OK")
        {
            table.Rows[index].SetField("Alias", "YES");
        }else if (table.Rows[index].Field<string>("ColumnGroup") == "NO")
        {
            table.Rows[index].SetField("Alias", "NO");
        }
    }
}

Once you have the above do a group by.