CODEWITHSUNDEEP
c#linq
calvin creater
calvin creater

Reputation: 1

Dynamic Left join 2 datatable

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

Upvotes: 0

Views: 410

Answers (3)

Mike Hofer
Mike Hofer

Reputation: 17022

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.

Upvotes: 0

Dionis Takac
Dionis Takac

Reputation: 91

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.

Upvotes: 0

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27282

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,
    };

Upvotes: 2

Related Questions