Left join two datatables asp net c# LINQ

Question

I have an issue with joining two datatables for example:

table1        table2
ID, name       ID, stock
1, item1       1, blabla
1, item2       3, bla2   
3, item3

After left join it should looking like this:

   table3       
ID, name, stock       
1, item1, blabla       
1, item2, blabla         
3, item3, bla2

but it looks like this:

   table3    
ID, name, stock       
1, item1,        
1, item2,         
3, item3,

This is mine code:

var rowDataLeftOuter = from rowLeft in dtblLeft.AsEnumerable()
                       join rowRight in dtblRight.AsEnumerable() on rowLeft[colToJoinOn] equals rowRight[strTempColName] into gj
                       from subRight in gj.DefaultIfEmpty()
                       select rowLeft.ItemArray.Concat((subRight == null) ? (dtblRight.NewRow().ItemArray) : subRight.ItemArray).ToArray();

foreach (object[] values in rowDataLeftOuter)
                    dtblResult.Rows.Add(values);

Orginal code

colToJoinOn = id and strTempColName = id_2 => that means its ID from table2 — Adriano, Jan 04 '18 at 12:37
If your making a left join between two tables and only the left part has values, maybe it is because your join condition is wrong. — bruno.almeida, Jan 04 '18 at 14:22
no it's good because it's checked. Both table have the same column name. — Adriano, Jan 04 '18 at 14:55

TheRock · Answer 1 · 2018-01-04T22:38:28.847

Not sure what the value of the columns are in the join - colToJoinOn, strTempColName However the following gives the result you're looking for:

        const string columnNameId = "Id";

        var dtblLeft = new DataTable();
        dtblLeft.Columns.Add(new DataColumn(columnNameId, typeof(int)));
        dtblLeft.Columns.Add(new DataColumn("Name", typeof(string)));

        var dr1 = dtblLeft.NewRow();
        dr1[columnNameId] = 1;
        dr1["Name"] = "item1";
        dtblLeft.Rows.Add(dr1);
        var dr2 = dtblLeft.NewRow();
        dr2[columnNameId] = 1;
        dr2["Name"] = "item2";
        dtblLeft.Rows.Add(dr2);
        var dr3 = dtblLeft.NewRow();
        dr3[columnNameId] = 3;
        dr3["Name"] = "item3";
        dtblLeft.Rows.Add(dr3);

        var dtblRight = new DataTable();
        dtblRight.Columns.Add(new DataColumn(columnNameId, typeof(int)));
        dtblRight.Columns.Add(new DataColumn("Stock", typeof(string)));

        var dr4 = dtblRight.NewRow();
        dr4[columnNameId] = 1;
        dr4["Stock"] = "blabla";
        dtblRight.Rows.Add(dr4);
        var dr5 = dtblRight.NewRow();
        dr5[columnNameId] = 3;
        dr5["Stock"] = "bla2";
        dtblRight.Rows.Add(dr5);

        var dtblResult = new DataTable();
        dtblResult.Columns.Add(new DataColumn(columnNameId, typeof(int)));
        dtblResult.Columns.Add(new DataColumn("Name", typeof(string)));
        dtblResult.Columns.Add(new DataColumn("Stock", typeof(string)));

        var result = from rowLeft in dtblLeft.AsEnumerable()
            join rowRight in dtblRight.AsEnumerable() on rowLeft[columnNameId] equals rowRight[columnNameId] into gj
            from subRight in gj.DefaultIfEmpty()
            select dtblResult.NewRow().ItemArray = new[]
            {
                rowLeft[columnNameId],
                rowLeft["Name"],
                subRight?["Stock"] ?? ""
            };

        foreach (var dataRow in result)
        {
            dtblResult.Rows.Add(dataRow);
        }

Left join two datatables asp net c# LINQ

1 Answers1