Dynamic Left join 2 datatable

Question

I have 2 C# datatable:

Datatable A:

id	Name	Age	Height
01	Pauls	22	170
02	Sam	20	175
03	Mike	20	175
04	Jame	23	180

Datatable B:

id	Height	Age
01	175	23
02	190	21

The question here is how could I get this output by join 2 table A & B by id and get output datatable using Linq in C# OutputTable:

id	Name	Age	Height
01	Pauls	23(value in table B)	175 (value in table B)
02	Sam	21(value in table B)	190 (value in table B)
03	Mike	20	175
04	Jame	23	180

My code here:

var results = from ta in tableA.AsEnumerable()
  join tb in tableB.AsEnumerable on ta["id"] equals tb["id"]
  select new 
  {
    ta["id"],
    ta["Name"],
    tb["Age"],
    tb["Height"]
    };

My output so far (not what I expected):

id	Name	Age	Height
01	Pauls	23(value in table B)	175 (value in table B)
02	Sam	21(value in table B)	190 (value in table B)

P/s: above table is just an example data, true data is bigger

score 2 · Answer 1 · answered Aug 19 '21 at 09:50

2

INNER JOIN works as filter if record not found on the right side. You have to use LEFT JOIN here, which is implemented in LINQ as GroupJoin with SelectMany:

var results = from ta in tableA
    join tb in tableB on ta.id equals tb.id into g
    from tb in g.DefaultIfEmpty()
    select new 
    {
        ta.id,
        ta.Name,
        Age = tb != null ? tb.Age : ta.Age,
        Height = tb != null ? tb.Height : ta.Height,
    };

answered Aug 19 '21 at 09:50

Svyatoslav Danyliv

21,911
3
16
32

and how to add results for output table? – calvin creater Aug 19 '21 at 10:28
I didn't get question. LINQ query selects data but not add something. – Svyatoslav Danyliv Aug 19 '21 at 10:36
My expected output is an datatable so please help!!! – calvin creater Aug 19 '21 at 11:03
Check this [Miscrosoft sample](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/implement-copytodatatable-where-type-not-a-datarow?redirectedfrom=MSDN) – Svyatoslav Danyliv Aug 19 '21 at 11:06

Dionis Takac · Answer 2 · 2021-08-19T10:16:26.463

0

Could you try something like this:

  var results = from ta in tableA
  join tb in tableB on ta.id equals tb.id into gj
  from subTableA in gj.DefaultIfEmpty()
  select new 
  {
    id = ta.id,
    Name = ta.Name,
    Age = (subTableA == null ? ta.Age : tb.Age + "(value in table B)"),
    Height = (subTableA == null ? ta.Height : tb.Height + "(value in table B)") 
    };

Here is some page about left join using Entity Framework - https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins

Idea is to introduce row 'from subTableA in gj.DefaultIfEmpty()' in order to get all rows from the join, even those which don't have matching value in second table.

edited Aug 19 '21 at 10:16

answered Aug 19 '21 at 10:04

Dionis Takac

91
5

thanks for your help. But could you help me on adding the results into datatable for the output? – calvin creater Aug 19 '21 at 11:05
not sure if I understand, you want to create new database table from the result table? – Dionis Takac Aug 19 '21 at 13:12
My expected output like I asked in question is a Datatable with data is output joined result – calvin creater Aug 19 '21 at 19:44

score 0 · Answer 3 · answered Aug 19 '21 at 20:17

0

If I understand the question correctly, you want to get back a DataTable from your LINQ query. However, LINQ returns an IEnumerable<T> or an IQueryable<T> if you're querying a database.

If you want a DataTable, you'll need some additional code. I suggest you refer to the top answer on this StackOverflow question, which provides a solution for converting an IEnumerable to a DataTable.

answered Aug 19 '21 at 20:17

Mike Hofer

16,477
11
74
110

I'm really appreciate for your help, but my problems here is the output result as IEnumerable output is missing some column (ex: A Datatable contain 5 rows, B DataTable contain 2 rows, after joined by id, the result only have 2 rows from joined result of A and B) – calvin creater Aug 19 '21 at 20:39

Dynamic Left join 2 datatable

3 Answers3