Join two datatables with Linq

Question

I am trying to join two datatables which I have nearly done after searching the web for examples.

The problem I have is it only shows the rows where there is a match; how do I show all of the rows in t1 and then if there is a match show something in a column to say there is a match?

Below is the code I am using:

Dim Query = From t1 In dt.AsEnumerable() Join t2 In dt2.AsEnumerable()
        On t1.Field(Of String)("Surname") Equals t2.Field(Of String)("VisitorFob")
            Select New With {.Surname = t1.Field(Of String)("Surname"), .VisitorFob = t2.Field(Of String)("VisitorFob")}

Dim newTable As New DataTable()

newTable.Columns.Add("Surname", GetType(String))
newTable.Columns.Add("VisitorFob", GetType(String))

For Each rowInfo In Query
    newTable.Rows.Add(rowInfo.Surname, rowInfo.VisitorFob)
Next

MUlferts · Accepted Answer · 2017-10-10T16:14:16.147

0

You could use a left outer join like with this question to include data from the first table regardless of matches.

LEFT OUTER JOIN gets all records from the first table, no matter if there is a match on the second table. If there is a match, it gets the values of the second table, if there is not a match then it returns NULL. article explaing some of the linq query methods

I haven't tested this but I think this would work for your query if your current query works:

   Dim Query = From t1 In dt.AsEnumerable() 
      Group Join t2 In dt2.AsEnumerable()
      On t1.Field(Of String)("Surname") Equals t2.Field(Of String)("VisitorFob")
      Into temp = Group
      From b In temp.DefaultIfEmpty()   
      Select New With {.Surname = t1.Field(Of String)("Surname"),
       .VisitorFob = If(b Is Nothing, String.Empty, b.Field(Of String)("VisitorFob"))}

    Dim newTable As New DataTable()

    newTable.Columns.Add("Surname", GetType(String))
    newTable.Columns.Add("VisitorFob", GetType(String))

    For Each rowInfo In Query
        newTable.Rows.Add(rowInfo.Surname, rowInfo.VisitorFob)
    Next

Your "VisitorFob" value returned from t2 would be null to indicate that there was no match to t1 as well since the left outer join would return the t1 values without the matching t2 values.

edited Oct 10 '17 at 16:14

answered Oct 10 '17 at 15:48

MUlferts

1,310
2
16
30

Thanks for this! It nearly works, although if there are rows where there are no matches I get this. System.ArgumentNullException: Value cannot be null. Parameter name: row – danieldunn10 Oct 10 '17 at 15:58
I also needed to change it to this .VisitorFob = b.Field(Of String)("VisitorFob") – danieldunn10 Oct 10 '17 at 15:59
No sorry, if I change my datatables so that both tables only have records in which match it works. But if table 1 has say a record with "Smith" in the surname column, and there is no Smith in the VisitorFob column in table 2. I get the value cannot be null error – danieldunn10 Oct 10 '17 at 16:07
You may just have to deal with null values in the select statement then. I will try to update my answer with something. – MUlferts Oct 10 '17 at 16:08
I am not entirely sure about the syntax as I have not worked with Vb in a long time, but I think that checking for null and setting an empty string if the b table returns null might be the right way to go. I added what I think the correct code. It is showing up without errors in my compiler. – MUlferts Oct 10 '17 at 16:15
Awesome, I actually learned something too. I have not seen much for LINQ in vb at all yet. Especially not special joins. – MUlferts Oct 10 '17 at 16:18

Join two datatables with Linq

1 Answers1