Making query with 2 datatables

Question

I have 2 datatables:

 dt1:                       dt2:
   | id | num | value |       | id | num | name      |
   |----+-----+-------|       |----+-----+-----------|
   |  99|    1|  +    |       | 99 |    1| tiger     |
   | 100|    2|  +    |       | 100|    2| pigeon    |
   | 101|    1|  -    |       | 101|    1| crocodile |
   | 102|    1|  +    |       | 102|    1| panther   |
                              | 105|    1| whale     |

And I want to bind it to one datatable:

| id | num | value |  name    |
|----+-----+-------+----------|
| 99 |    1|    +  |tiger     |
| 100|    2|    +  |pigeon    |
| 101|    1|    -  |crocodile |
| 102|    1|    +  |panther   |
| 105|    1|       |whale     |

After that I will bind DataRow to the dataGridView.

I tried to create a query, but something wrong in it, it doesn't return a value:

var vquery = (from dt1 in dtable1.AsEnumerable()
              from dt2 in dtable2.AsEnumerable()
              where dt1.Field<int?>(IndexesField.F_LINK_ID) == dt2.Field<int?>(IndexesField.F_LINK_ID)
              where dt1.Field<int?>(IndexesField.F_TABKEY) == dt2.Field<int?>(IndexesField.F_TABKEY)
              select new { dt1, dt2 });

What I need to do to correct it?

Regards, Alexander.

Answer 1

If you want to use LINQ you will have to simulate an outer join to get the desired values, then insert them into a 3rd datatable.

void Main()
{
    DataTable dTable1 = new DataTable();
    DataTable dTable2 = new DataTable();

    dTable1.Columns.Add("id", typeof(int));
    dTable1.Columns.Add("num", typeof(int));
    dTable1.Columns.Add("value", typeof(string));

    dTable2.Columns.Add("id", typeof(int));
    dTable2.Columns.Add("num", typeof(int));
    dTable2.Columns.Add("name", typeof(string));

    dTable1.Rows.Add(new object [] { 99, 1, "+"});
    dTable1.Rows.Add(new object [] { 100, 1, "+"});
    dTable1.Rows.Add(new object [] { 101, 1, "-"});
    dTable1.Rows.Add(new object [] { 102, 1, "+"});

    dTable2.Rows.Add(new object [] { 99, 1, "tiger"});
    dTable2.Rows.Add(new object [] { 100, 1, "pigeon"});
    dTable2.Rows.Add(new object [] { 101, 1, "crocodile"});
    dTable2.Rows.Add(new object [] { 102, 1, "panther"});
    dTable2.Rows.Add(new object [] { 105, 1, "whale"});

    var vQuery = (from dt1 in dTable1.AsEnumerable()
                      join dt2 in dTable2.AsEnumerable()
                      on new { Id = dt1.Field<int?>(0), Num = dt1.Field<int?>(1) } 
                    equals new { Id = dt2.Field<int?>(0), Num = dt2.Field<int?>(1) } 
                    into temp
                    from defaultDt2 in temp.DefaultIfEmpty(null)
              select new {
                      id = (dt1 ?? temp.First()).Field<int?>(0), 
                    num = (dt1 ?? temp.First()).Field<int?>(1), 
                    value = dt1 != null ? dt1.Field<string>(2) : null,
                    name = temp.First() != null ? temp.First().Field<string>(2) : null
              }).Concat(
                (from dt1 in dTable2.AsEnumerable()
                      join dt2 in dTable1.AsEnumerable()
                      on new { Id = dt1.Field<int?>(0), Num = dt1.Field<int?>(1) } 
                    equals new { Id = dt2.Field<int?>(0), Num = dt2.Field<int?>(1) } 
                                        into temp
                    from defaultDt2 in temp.ToList<DataRow>().DefaultIfEmpty(null)
              select new 
              {
                      id = (temp.FirstOrDefault() ?? dt1).Field<int?>(0), 
                    num = (temp.FirstOrDefault() ?? dt1).Field<int?>(1), 
                    value = temp.FirstOrDefault() != null ? temp.FirstOrDefault().Field<string>(2) : null,
                    name = dt1 != null ? dt1.Field<string>(2) : null
              }));

    // At this point you will have the data you're after.
}

Answer 2

You should use DataTable.Merge instead of using LINQ here.
If your DataTables have a primary key then it will automatically figure it out like you want.

dtable1.PrimaryKey = new[] { dtable1.Columns["id"] };
dtable2.PrimaryKey = new[] { dtable2.Columns["id"] };
dtable1.Merge(dtable2);