Q:

Left Join/merge datatable c# with non-unique columns

I have two datatables which has different structure, A table has a column call "Campaign ID" which is not unique, I would like to join it on the B table's "Campaign ID" which is unique.
so its equivalent to whats in sql
select * from 
A left join B on 
A.[Campagin ID] = B.[Cmpaign ID]

I've tried datatable.merge does not work because it can only merge base on unique column fields.
I've tried Linq and Lamda. 
var resultDt = from c in dt.AsEnumerable()
                       join lookup in lookupDt.AsEnumerable() on c["Campaign ID"].ToString() equals lookup["EventID"]
                           .ToString() into results
                       from r in results.DefaultIfEmpty()
                       select new { a=c, b =lookup };

it returns two set of datarow instead of one set of datarow.
I also tried with dictionary but its too expensive to run.
Expected result
If i select r it will return just table B value
I expected the output would be like
select * from 
A left join B on 
A.[Campagin ID] = B.[Cmpaign ID]

in SQL
if Table A is like
Campaign ID                            Description      Number
eda1e64c-0002-4000-8000-000000000198            
eda1e64c-0002-4000-8000-000000000198            
eda1e64c-0002-4000-8000-000000000198            
eda1e64c-0002-4000-8000-000000000198            
eda1e64c-0002-4000-8000-000000000000    Testing 123     1111
                                        Description 2   3333

Table B is like
Campaign ID                             Name      
eda1e64c-0002-4000-8000-000000000198    Test Name1  
eda1e64c-0002-4000-8000-000000000000    Test Name2      

Expected Result
Campaign ID                             Description      Number   Name
eda1e64c-0002-4000-8000-000000000198                             Test Name1
eda1e64c-0002-4000-8000-000000000198                             Test Name1
eda1e64c-0002-4000-8000-000000000198                             Test Name1
eda1e64c-0002-4000-8000-000000000198                             Test Name1
eda1e64c-0002-4000-8000-000000000000    Testing 123     1111     Test Name2

Is there any default c# method I can use, or any efficient way to do it?
Thanks so much for all the helps.

A:

I think you’re almost there, just turn your LINQ query output into an object array and bang it into a new datatable as a separate thing; remember that LINQ is primarily intended to query and return collections of results, not modify existing things:
Left join using LINQ, manual output list, manual consumption into datatable
            var query =
                from ce in c.AsEnumerable()
                join le in lookup.AsEnumerable() on c.Field<Guid>("Campaign ID") equals le.Field<Guid>("Campaign ID") into cele
                from lenull in cele.DefaultIfEmpty()
                select new object[]
                {
                  ce.Field<Guid>("Campaign ID"),
                  ce.Field<string>("Description"),
                  ce.Field<int>("Number"), //don't know how your table has null here, maybe <int?>
                  lenull?.Field<string>("Name")
                };

            DataTable c = new DataTable(); //to hold results
            c.Columns.Add("Campaign ID", typeof(Guid)); 
            c.Columns.Add("Description"); 
            c.Columns.Add("Number", typeof(int)); 
            c.Columns.Add("Name");
            foreach (var at in query)
                c.Rows.Add(at);

Because lenull is potentially null, I use null propagator to avoid a null reference exception trying to get the field of a null row. We can do this dynamically too, without reflection, but it's a lot slower. For the following examples I used my own simple pair of datatables, set up like this:
        //setup part
        DataTable a = new DataTable();
        a.Columns.Add("ID", typeof(int));
        a.Columns.Add("Name", typeof(string));
        a.Columns.Add("Age", typeof(int));
        DataTable b = new DataTable();
        var pk = b.Columns.Add("ID", typeof(int));
        b.Columns.Add("Address", typeof(string));
        b.Columns.Add("YearsAt", typeof(int));
        b.PrimaryKey = new[] { pk };

        a.Rows.Add(1, "John", 22);
        a.Rows.Add(2, "Mary", 33);
        a.Rows.Add(3, "Bill", 44);

        b.Rows.Add(1, "JohnAddr", 3);
        b.Rows.Add(2, "MaryAddr", 4);

Left Join with LINQ, manual output list, dynamic consumption
            var query =
                from ae in a.AsEnumerable()
                join be in b.AsEnumerable() on ae.Field<int>("ID") equals be.Field<int>("ID_") into aebe
                from be2 in aebe.DefaultIfEmpty()
                select new Dictionary<string, object>
                {
                    {"ID", ae.Field<int>("ID")},
                    {"Name", ae.Field<string>("Name") },
                    {"Age", ae.Field<int>("Age") },
                    {"Address", be2?.Field<string>("Address") },
                    {"YearsAt", be2?.Field<int>("YearsAt") }
                };

            //setup datatable
            DataTable c = new DataTable();                    

            int keyCount = query.First().Keys.Count; //track columns needed to be added
            foreach (var dict in query)
            {
                var ro = c.NewRow();
                foreach (string key in dict.Keys)
                {
                    if (keyCount > 0 && dict[key] != null && !c.Columns.Contains(key))
                    { //if the column is not in the table, and the value isnt null (so we can deduce the type)
                        c.Columns.Add(key, dict[key].GetType());
                        keyCount--; //mark it as added. Eventually this will hit 0 and we won't evaluate the other two clauses
                    }

                    if (dict[key] != null) //don't store nulls
                        ro[key] = dict[key];
                }
                c.Rows.Add(ro);
            } 

Of course, you may complain that you still have to specify all the columns you want out in the LINQ query select. We can make that dynamic too:
Left Join with LINQ, dynamic output list, dynamic consumption
             var query =
                from ae in a.AsEnumerable()
                join be in b.AsEnumerable() on ae.Field<int>("ID") equals be.Field<int>("ID_") into aebe
                from be2 in aebe.DefaultIfEmpty()
                select MapToDict(ae, be2);

            //setup datatable
            DataTable c = new DataTable();                    

            int keyCount = query.First().Keys.Count;
            foreach (var dict in query)
            {
                //have we got all our columns addded yet?
                var ro = c.NewRow();
                foreach (string key in dict.Keys)
                {
                    if (keyCount > 0 && dict[key] != null && !c.Columns.Contains(key))
                    { //if the column is not in the table, and the value isnt null (so we can deduce the type)
                        c.Columns.Add(key, dict[key].GetType());
                        keyCount--; //mark it as added. Eventually this will hit 0 and we won't evaluate the other two clauses
                    }

                    if (dict[key] != null) //don't store nulls
                        ro[key] = dict[key];
                }
                c.Rows.Add(ro);
            }

I’ve never liked joins on DataTables in LINQ, I’ve always preferred to:

establish a primary key on b
add new columns to a that duplicate b’s column name and type (rename b column adding an int if there is a name clash)
iterate over a, calling b.Find(some column from a)
if find didn’t return null, for each column in b, set the same named column in the a row to the value in the b row that find gave you

Here's the code that does the above:
Left Join using loops
        //ensure unique named columns in b, and grow a's columns
        foreach (DataColumn bcol in b.Columns) {
            while (a.Columns.Contains(bcol.ColumnName))
                bcol.ColumnName += "_";
            a.Columns.Add(bcol.ColumnName, bcol.DataType);
        }

        //perform left join
        foreach (DataRow aro in a.Rows) {
            var f = b.Rows.Find(aro["ID"]);
            if (f != null)
                foreach (DataColumn bcol in b.Columns)
                    aro[bcol.ColumnName] = f[bcol];
        }

It would probably be fairly trivial to turn this into an extension method so that any table could have another one joined onto it like a.LeftJoin(b, aID: "ID", bID: "ID") .. If you wanted a more convoluted logic than simple equals, then some code changes would be necessary.
For curiousity I tried all 4 methods back to back, timing them. In my context loops were about 2.5 times faster than LINQ with a fixed structure and hardcoded column names, and 4 times faster than using dictionaries to make things dynamic:
        for (int lc = 0; lc < 10; lc++) {

            //setup 100K rows
            DataTable a = new DataTable();
            a.Columns.Add("ID", typeof(int));
            a.Columns.Add("Name", typeof(string));
            a.Columns.Add("Age", typeof(int));
            DataTable b = new DataTable();
            var pk = b.Columns.Add("ID", typeof(int));
            b.Columns.Add("Address", typeof(string));
            b.Columns.Add("YearsAt", typeof(int));
            b.PrimaryKey = new[] { pk };

            Random r = new Random();
            for (int i = 0; i < 100000; i++)
            {
                a.Rows.Add(i, Guid.NewGuid().ToString(), r.Next(20, 99));
                if (r.Next(0, 9) < 1)
                    b.Rows.Add(i, Guid.NewGuid().ToString(), r.Next(1, 10));

            }

            Stopwatch sw = Stopwatch.StartNew();

### INSERT CHOSEN METHOD HERE ###

            sw.Stop();

            Console.WriteLine($"Time: {sw.ElapsedMilliseconds}ms");

        }

The results were typically 80ms for loops to process 100K rows, and 200ms for LINQ hardcodes (manual select, manual table) and 400ms for LINQ dictionaries (dynamic something) methods.

