Left Join Linq

Commenter

Contenu du snippet

'Bonjour,
'je vous propose une class permettant de faire un left join entre deux datatable
'j'ai trouvé une solution qui fonctionne mais il y a surement mieux
'Je poste ici ce code car il y a beaucoup de recherche sur google pour ce genre de chose

'n'hésitez pas à faire des proposition d'amélioration 

Public Class joinTables
        Private FirstTab As DataTable, SecondTable As DataTable, Champ1 As String, Champ2 As String
        Private Cols1 As List(Of String), Cols2 As List(Of String)
        Private RDTab As DataTable

        Public Sub New(_FirstTab As DataTable, _SecondTable As DataTable, _Champ1 As String, _Champ2 As String)
            FirstTab = _FirstTab
            SecondTable = _SecondTable
            Champ1 = _Champ1
            Champ2 = _Champ2
            Cols1 = New List(Of String)
            Cols2 = New List(Of String)
            RDTab = New DataTable
            For Each Col As DataColumn In FirstTab.Columns
                Cols1.Add(Col.ColumnName)
                RDTab.Columns.Add(New DataColumn(Col.ColumnName, Col.DataType, Col.Expression))
            Next
            For Each Col As DataColumn In From _Col As DataColumn In SecondTable.Columns.OfType(Of DataColumn)() Where Not Cols1.Contains(_Col.ColumnName)
                Cols2.Add(Col.ColumnName)
                RDTab.Columns.Add(New DataColumn(Col.ColumnName, Col.DataType, Col.Expression))
            Next
        End Sub

        Public Function leftJoin() As DataTable
            Dim query = (From FRow As DataRow In FirstTab.Rows.OfType(Of DataRow)() _
                        Group Join SRow As DataRow In SecondTable.Rows.OfType(Of DataRow)() _
                        On FRow(Champ1) Equals SRow(Champ1) And FRow(Champ2) Equals SRow(Champ2) _
                        Into G = Group _
                        From GRow As DataRow In G.DefaultIfEmpty
                        Select getNewRow(FRow, GRow))
            Dim Q = From Row In query _
                    Group By C1 = Row(Champ1), C2 = Row(Champ2) _
                    Into G = Group
                    Let first = G.First
                    Select first
            Return New DataTable(Q.OfType(Of DataRow)().CopyToDataTable())
        End Function

        Private Function getNewRow(Row1 As DataRow, Row2 As DataRow) As DataRow
            Dim R As DataRow = RDTab.addRow
            Try
                If Row1 IsNot Nothing Then
                    For Each Col As String In Cols1
                        R(Col) = Row1(Col)
                    Next
                End If
                If Row2 IsNot Nothing Then
                    For Each Col As String In Cols2
                        R(Col) = Row2(Col)
                    Next
                End If
            Catch ex As Exception
                addError(ex)
            End Try
            Return R
        End Function

        Public Function Join() As DataTable
                Dim query = (From FRow As DataRow In FirstTab.Rows.OfType(Of DataRow)() _
                        Join SRow As DataRow In SecondTable.Rows.OfType(Of DataRow)() _
                        On FRow(Champ1) Equals SRow(Champ1) And FRow(Champ2) Equals SRow(Champ2) _
                        Select getNewRow(FRow, SRow))
                Dim Q = From Row In query _
                        Group By C1 = Row(Champ1), C2 = Row(Champ2) _
                        Into G = Group
                        Let first = G.First
                        Select first
                Return New DataTable(Q.OfType(Of DataRow)().CopyToDataTable())
        End Function
    End Class

    Public Class joinTables1
        Private FirstTab As DataTable, SecondTable As DataTable, Champ1 As String
        Private Cols1 As List(Of String), Cols2 As List(Of String)
        Private RDTab As DataTable

        Public Sub New(_FirstTab As DataTable, _SecondTable As DataTable, _Champ1 As String)
            FirstTab = _FirstTab
            SecondTable = _SecondTable
            Champ1 = _Champ1
            Cols1 = New List(Of String)
            Cols2 = New List(Of String)
            RDTab = New DataTable
            For Each Col As DataColumn In FirstTab.Columns
                Cols1.Add(Col.ColumnName)
                RDTab.Columns.Add(New DataColumn(Col.ColumnName, Col.DataType, Col.Expression))
            Next
            For Each Col As DataColumn In From _Col As DataColumn In SecondTable.Columns.OfType(Of DataColumn)() Where Not Cols1.Contains(_Col.ColumnName)
                Cols2.Add(Col.ColumnName)
                RDTab.Columns.Add(New DataColumn(Col.ColumnName, Col.DataType, Col.Expression))
            Next
        End Sub

        Public Function leftJoin() As DataTable
            Dim query = (From FRow As DataRow In FirstTab.Rows.OfType(Of DataRow)() _
                        Group Join SRow As DataRow In SecondTable.Rows.OfType(Of DataRow)() _
                        On FRow(Champ1) Equals SRow(Champ1) _
                        Into G = Group _
                        From GRow As DataRow In G.DefaultIfEmpty
                        Select getNewRow(FRow, GRow))
            Dim Q = From Row In query _
                    Group By C1 = Row(Champ1) _
                    Into G = Group
                    Let first = G.First
                    Select first
            Return New DataTable(Q.OfType(Of DataRow)().CopyToDataTable())
        End Function

        Public Function Join() As DataTable
            Dim query = (From FRow As DataRow In FirstTab.Rows.OfType(Of DataRow)() _
                        Join SRow As DataRow In SecondTable.Rows.OfType(Of DataRow)() _
                        On FRow(Champ1) Equals SRow(Champ1) _
                        Select getNewRow(FRow, SRow))
            Dim Q = From Row In query _
                    Group By C1 = Row(Champ1) _
                    Into G = Group
                    Let first = G.First
                    Select first
            Return New DataTable(Q.OfType(Of DataRow)().CopyToDataTable())
        End Function

        Private Function getNewRow(Row1 As DataRow, Row2 As DataRow) As DataRow
            Dim R As DataRow = RDTab.addRow
            Try
                If Row1 IsNot Nothing Then
                    For Each Col As String In Cols1
                        R(Col) = Row1(Col)
                    Next
                End If
                If Row2 IsNot Nothing Then
                    For Each Col As String In Cols2
                        R(Col) = Row2(Col)
                    Next
                End If
            Catch ex As Exception
                addError(ex)
            End Try
            Return R
        End Function
    End Class

Compatibilité : 1

A voir également

S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.

Du même auteur (cs_eldim)