Left excluding join using LINQ in vb.net

user_14632437 0
Viewed 110

I have 2 DataTables:

TableA:

id | name | phone
-----------------
1  | Paul | 8523      
2  | John | 5217     
3  | Stan | 5021

TableB:

id
--
2
5

I want to do a left excluding join between them: I want a DataTable object with all rows of TableA which have a corresponding id Column in TableB.

The result I want to have is this:

id | name | phone
-----------------
1  | Paul | 8523
3  | Stan | 5021

I already have done this with for loops, but I think it could be simpler if I used LINQ. The problem is that all I've tried doesn't work.

How can I do this using LINQ?

The code with for loops is this:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim table = LeftExcludingJoin()

    If table IsNot Nothing Then
        For Each row As DataRow In table.Rows
            Console.WriteLine(row.Item("id") & " - " & row.Item("name") & " - " & row.Item("phone"))
        Next
    End If

End Sub

Private Function LeftExcludingJoin() As DataTable
    Dim tableA As New DataTable
    tableA.Columns.Add("id")
    tableA.Columns.Add("name")
    tableA.Columns.Add("phone")

    Dim tableB As New DataTable
    tableB.Columns.Add("id")

    'Rows for tableA
    Dim rowA1 As DataRow = tableA.NewRow
    rowA1.Item("id") = 1
    rowA1.Item("name") = "Paul"
    rowA1.Item("phone") = 8523
    tableA.Rows.Add(rowA1)

    Dim rowA2 As DataRow = tableA.NewRow
    rowA2.Item("id") = 2
    rowA2.Item("name") = "John"
    rowA2.Item("phone") = 5217
    tableA.Rows.Add(rowA2)

    Dim rowA3 As DataRow = tableA.NewRow
    rowA3.Item("id") = 3
    rowA3.Item("name") = "Stan"
    rowA3.Item("phone") = 5021
    tableA.Rows.Add(rowA3)

    'Rows for tableB
    Dim rowB1 As DataRow = tableB.NewRow
    rowB1.Item("id") = 2
    tableB.Rows.Add(rowB1)

    Dim rowB2 As DataRow = tableB.NewRow
    rowB2.Item("id") = 5
    tableB.Rows.Add(rowB2)

    'Get rows in A which are not in B
    Dim tableResult = tableA.Clone

    If tableA IsNot Nothing Then
        If tableB IsNot Nothing Then
            For Each rowA As DataRow In tableA.Rows
                Dim coincidence As Boolean = False
                For Each rowB As DataRow In tableB.Rows
                    If rowA.Item("id") = rowB.Item("id") Then
                        coincidence = True
                        Exit For
                    End If
                Next
                If coincidence = False Then
                    Dim newResultRow As DataRow = tableResult.NewRow
                    newResultRow.Item("id") = rowA.Item("id")
                    newResultRow.Item("name") = rowA.Item("name")
                    newResultRow.Item("phone") = rowA.Item("phone")
                    tableResult.Rows.Add(newResultRow)
                End If
            Next
        Else
            'All tableA values are part of the result because tableB is Nothing.
            Return tableA
        End If
        Return tableResult
    Else
        Return Nothing
    End If
End Function
datatable vb.net linq .net
2 Answers

An intro, to simplify the procedure (could be useful in other cases):

  • Define the Columns data type explicitly:

    tableA.Columns.Add("id", GetType(Integer))

  • A phone number (code) is a string, not an integer (maybe phone is just an example, never mind):

    tableA.Columns.Add("phone", GetType(String))

  • A Row can be added using the overload that accepts a paramarray as object(). It can be a sequence of values or an Object() array:

    tableA.Rows.Add(1, "Paul", "8523")

The System.Data.DataSetExtensions.dll assembly contains extension methods that allow to apply LINQ queries (which operate on IEnumerable<T> collections) to DataTables, using the AsEnumerable() extension method.
This assembly is usually included in new Projects, along with the System.Data assembly. If it's not there, add a Project reference.

After that, the usual filter conditions (Where(), Any(), All() etc.) can be used on the IEnumerable(Of DataRow) objects that the extension method generates.

The IEnumerable results of the query (EnumerableRowCollection<TRow>) can be converted into a DataTable using the CopyToDataTable() extension method.

  • We want to include Rows in the first DataTable Where() the id Column is not equal to Any() id Column of a second Datatable and create a new DataTable using this filter:
    (of course you can construct a filter that applies the same conditions in a different way)
Private Function LeftExcludingJoin() As DataTable
    Dim tableA As New DataTable("TableA")
    tableA.Columns.Add("id", GetType(Integer))
    tableA.Columns.Add("name", GetType(String))
    tableA.Columns.Add("phone", GetType(String))

    tableA.Rows.Add(1, "Paul", "8523")
    tableA.Rows.Add(2, "John", "5217")
    tableA.Rows.Add(3, "Stan", "5021")

    Dim tableB As New DataTable("TableB")
    tableB.Columns.Add("id", GetType(Integer))

    tableB.Rows.Add(2)
    tableB.Rows.Add(5)

    Dim rows = tableA.AsEnumerable().
        Where(Function(drA) Not tableB.AsEnumerable().
            Any(Function(drB) CInt(drB("id")) = CInt(drA("id"))))

    Return If(rows.Count() = 0, Nothing, rows.CopyToDataTable())
End Function
user_74441030

In addition to Jimi's very good answer, it is also possible to use the LINQ Enumerable.Except method. It just needs a helper class to compare the appropriate element(s) from each datatable's rows.

Shamelessly using parts of the code from that answer:

Public Class IdComparer
    Implements IEqualityComparer(Of DataRow)

    Public Function Equals1(ByVal x As DataRow, ByVal y As DataRow) As Boolean Implements IEqualityComparer(Of DataRow).Equals
        If x Is y Then
            Return True
        End If

        If x Is Nothing OrElse y Is Nothing Then
            Return False
        End If

        Return (x.Field(Of Integer)("id") = y.Field(Of Integer)("id"))

    End Function

    Public Function GetHashCode1(ByVal dr As DataRow) As Integer Implements IEqualityComparer(Of DataRow).GetHashCode
        Return If(dr Is Nothing, 0, dr.Field(Of Integer)("id").GetHashCode())

    End Function

End Class

Private Function LeftExcludingJoin() As DataTable
    Dim tableA As New DataTable("TableA")
    tableA.Columns.Add("id", GetType(Integer))
    tableA.Columns.Add("name", GetType(String))
    tableA.Columns.Add("phone", GetType(String))

    tableA.Rows.Add(1, "Paul", "8523")
    tableA.Rows.Add(2, "John", "5217")
    tableA.Rows.Add(3, "Stan", "5021")

    Dim tableB As New DataTable("TableB")
    tableB.Columns.Add("id", GetType(Integer))

    tableB.Rows.Add(2)
    tableB.Rows.Add(5)

    Dim q = tableA.AsEnumerable().Except(tableB.AsEnumerable(), New IdComparer())

    Return If(q.Count = 0, Nothing, q.CopyToDataTable())

End Function
user_11153600
Related