Linq 连接参数化的不同键

Question

我正在尝试基于动态键 LINQ 两个表。用户可以通过组合框更改密钥。键可能是钱、字符串、双精度数、整数等。目前我得到的数据很好，但没有过滤掉双精度数。我可以在 VB 中过滤双精度值，但速度很慢。我想立即在 LINQ 查询中执行此操作。

数据如下：

第一个表：

 -------------------------------------------------------------
| AppleIndex  | AppleCost  | AppleColor  | AppleDescription   |
 ------------------------------------------------------------
|     1       |     3      | Red         | This is an apple   |
|     2       |     5      | Green       | This is an apple   |
|     3       |     4      | Pink        | This is an apple   |
|     4       |     2      | Yellow      | This is an apple   |
|     5       |     2      | Orange      | This is an apple   |
|     1       |     3      | Red         | This is a duplicate|
|     2       |     5      | Green       | This is a duplicate|
|     3       |     4      | Pink        | This is a duplicate|
|     4       |     2      | Yellow      | This is a duplicate|
|     5       |     2      | Orange      | This is a duplicate|
 -------------------------------------------------------------

第二个表：

 ------------------------------------------------------------
| OrangeIndex | OrangeCost | OrangeColor | OrangeDescription |
 ------------------------------------------------------------
|     1       |     1      | Orange      | This is an Orange |
|     2       |     3      | Orange      |                   |
|     3       |     2      | Orange      | This is an Orange |
|     4       |     3      | Orange      |                   |
|     5       |     2      | Orange      | This is an Orange |
 ------------------------------------------------------------

当前，我使用以下代码获取太多数据：

Dim Matches = From mRows In LinqMasterTable Join sRows In LinqSecondTable _
              On mRows(ThePrimaryKey) Equals sRows(TheForignKey) _
              Order By mRows(ThePrimaryKey) _
              Select mRows, sRows Distinct

结果：

 -------------------------------------------------------------------------
| 1  | 3 | Red    | This is an apple     | 1 | Orange | This is an Orange |
| 1  | 3 | Red    | This is an duplicate | 1 | Orange | This is an Orange |
| 2  | 5 | Green  | This is an apple     | 3 | Orange |                   |
| 2  | 5 | Green  | This is an duplicate | 3 | Orange |                   |
| 3  | 4 | Pink   | This is an apple     | 2 | Orange | This is an Orange |
| 3  | 4 | Pink   | This is an duplicate | 2 | Orange | This is an Orange |
| 4  | 2 | Yellow | This is an apple     | 3 | Orange |                   |
| 4  | 2 | Yellow | This is an duplicate | 3 | Orange |                   |
| 5  | 2 | Orange | This is an apple     | 2 | Orange | This is an Orange |
| 5  | 2 | Orange | This is an duplicate | 2 | Orange | This is an Orange |
 -------------------------------------------------------------------------

期望的结果：

 ------------------------------------------------------------------------
| 1 | 3 | Red    | This is an apple | 1 | 1 | Orange | This is an Orange |
| 2 | 5 | Green  | This is an apple | 2 | 3 | Orange |                   |
| 3 | 4 | Pink   | This is an apple | 3 | 2 | Orange | This is an Orange |
| 4 | 2 | Yellow | This is an apple | 4 | 3 | Orange |                   |
| 5 | 2 | Orange | This is an apple | 5 | 2 | Orange | This is an Orange |
 ------------------------------------------------------------------------

我尝试了以下操作：

'Get the original Column Names into an Array List
'MasterTableColumns = GetColumns(qMasterDS, TheMasterTable) '(external code)

'Plug the Existing DataSet into a DataView:
Dim View As DataView = New DataView(qMasterTable)

'Sort by the Primary Key:
View.Sort = ThePrimaryKey

'Build a new table listing only one column:
Dim newListTable As DataTable = _
View.ToTable("UniqueData", True, ThePrimaryKey)

这返回一个唯一的列表，但没有关联的数据：

 -------------
| AppleIndex  |
 -------------
|     1       | 
|     2       | 
|     3       |
|     4       |
|     5       |
 -------------

所以我尝试了这个：

'Build a new table with ALL the columns:
Dim newFullTable As DataTable = _
View.ToTable("UniqueData", True, _
     MasterTableColumns(0), _
     MasterTableColumns(1), _
     MasterTableColumns(2), _
     MasterTableColumns(3))

不幸的是，它产生以下结果...有重复：

 -------------------------------------------------------------
| AppleIndex  | AppleCost  | AppleColor  | AppleDescription   |
 ------------------------------------------------------------
|     1       |     3      | Red         | This is an apple   |
|     2       |     5      | Green       | This is an apple   |
|     3       |     4      | Pink        | This is an apple   |
|     4       |     2      | Yellow      | This is an apple   |
|     5       |     2      | Orange      | This is an apple   |
|     1       |     3      | Red         | This is a duplicate|
|     2       |     5      | Green       | This is a duplicate|
|     3       |     4      | Pink        | This is a duplicate|
|     4       |     2      | Yellow      | This is a duplicate|
|     5       |     2      | Orange      | This is a duplicate|
 -------------------------------------------------------------

有什么想法吗？

~~ ~~~~~~~~~~ 更新：~~~~~~~~~~~~

Jeff M 建议使用以下代码。 （谢谢杰夫）但是，它给了我一个错误。有谁知道在 VB 中实现此功能的语法吗？我已经搞砸了一点，但似乎无法做到正确。

Dim matches = _
    From mRows In (From row In LinqMasterTable _
        Group row By row(ThePrimaryKey) Into g() _
        Select g.First()) _
    Join sRows In LinqSecondTable _
    On mRows(ThePrimaryKey) Equals sRows(TheForignKey) _
    Order By mRows(ThePrimaryKey) _
    Select mRows, sRows

“row(ThePrimaryKey)”第三行中的错误：

“只能从不带参数的简单名称或限定名称推断范围变量名称。”

Answer 1

嗯，基本问题不是 LINQ。事实上，您的第一个表包含“重复项”，它们并不是真正的重复项，因为在您的示例中，每一行都是独特的。

因此，我们向您提出的问题是“我们如何识别原始表中的重复项？”。一旦回答了这个问题，剩下的事情就变得微不足道了。

例如（在 C# 中，因为我不确定 VB 语法）

var Matches = from mRows in LinqMasterTable
                             .Where(r=>r.AppleDescription=="This is an Apple")
              join sRows in LinqSecondTable 
                   on mRows(ThePrimaryKey) equals sRows(TheForignKey)  
              orderby mRows(ThePrimaryKey) 
              select new { mRows, sRows};

Answer 2

编辑：
以下是我编写 C# LINQ 查询的方式。这是一个替代版本，而不是使用 Distinct()，而是使用带有分组的嵌套查询，该查询应该具有类似的语义。它应该很容易转换为VB。

var matches = from mRows in (from row in LinqMasterTable
                             group row by row[ThePrimaryKey] into g
                             select g.First())
              join sRows in LinqSecondTable
                  on mRows[ThePrimaryKey] Equals sRows[TheForignKey]
              orderby mRows[ThePrimaryKey]
              select new { mRows, sRows }

以及我对上述内容的 VB 版本的尝试：

编辑：
至于最近的错误，我完全知道如何处理。当我玩VB LINQ时，我发现编译器不喜欢复杂的分组表达式。要解决这个问题，请将 row(ThePrimaryKey) 分配给临时变量并按该变量进行分组。那么它应该可以工作。

Dim matches = From mRows In (From row In LinqMasterTable _
                             Let grouping = row(ThePrimaryKey)
                             Group row By grouping Into g() _
                             Select g.First()) _
              Join sRows In LinqSecondTable _
                  On mRows(ThePrimaryKey) Equals sRows(TheForignKey) _
              Order By mRows(ThePrimaryKey) _
              Select mRows, sRows

事实上，经过第二次检查，发现分组依据需要一个名称。以下内容将起作用。

Dim matches = From mRows In (From row In LinqMasterTable _
                             Group row By Grouping = row(ThePrimaryKey) Into g() _
                             Select g.First()) _
              Join sRows In LinqSecondTable _
                  On mRows(ThePrimaryKey) Equals sRows(TheForignKey) _
              Order By mRows(ThePrimaryKey) _
              Select mRows, sRows

Answer 3

声明等：

Private Sub LinqTwoTableInnerJoin(ByRef qMasterDS As DataSet, _
                                  ByRef qMasterTable As DataTable, _
                                  ByRef qSecondDS As DataSet, _
                                  ByRef qSecondTable As DataTable, _
                                  ByRef qPrimaryKey As String, _
                                  ByRef qForignKey As String, _
                                  ByVal qResultsName As String)

Dim TheMasterTable As String = qMasterTable.TableName
Dim TheSecondTable As String = qSecondTable.TableName
Dim ThePrimaryKey As String = qPrimaryKey
Dim TheForignKey As String = qForignKey
Dim TheNewForignKey As String = ""

MasterTableColumns = GetColumns(qMasterDS, TheMasterTable)
SecondTableColumns = GetColumns(qSecondDS, TheSecondTable)

Dim mColumnCount As Integer = MasterTableColumns.Count
Dim sColumnCount As Integer = SecondTableColumns.Count

Dim ColumnCount As Integer = mColumnCount + sColumnCount

Dim LinqMasterTable = qMasterDS.Tables(TheMasterTable).AsEnumerable
Dim LinqSecondTable = qSecondDS.Tables(TheSecondTable).AsEnumerable

获取数据并按所选键对其进行排序：

Dim Matches = From mRows In LinqMasterTable Join sRows In LinqSecondTable _
             On mRows(ThePrimaryKey) Equals sRows(TheForignKey) _
             Order By mRows(ThePrimaryKey) _
             Select mRows, sRows

将结果放入数据集表中：

' Make sure the dataset is available and/or cleared:
If dsResults.Tables(qResultsName) Is Nothing Then dsResults.Tables.Add(qResultsName)
dsResults.Tables(qResultsName).Clear() : dsResults.Tables(qResultsName).Columns.Clear()

'Adds Master Table Column Names
For x = 0 To MasterTableColumns.Count - 1
    dsResults.Tables(qResultsName).Columns.Add(MasterTableColumns(x))
Next

'Rename Second Table Names if Needed:
For x = 0 To SecondTableColumns.Count - 1
    With dsResults.Tables(qResultsName)
        For y = 0 To .Columns.Count - 1
            If SecondTableColumns(x) = .Columns(y).ColumnName Then
                SecondTableColumns(x) = SecondTableColumns(x) & "_2"
            End If
        Next
    End With
Next

'Make sure that the Forign Key is a Unique Value
If ForignKey1 = PrimaryKey Then
    TheNewForignKey = ForignKey1 & "_2"
Else
    TheNewForignKey = ForignKey1
End If

'Adds Second Table Column Names
For x = 0 To SecondTableColumns.Count - 1 
    dsResults.Tables(qResultsName).Columns.Add(SecondTableColumns(x))
Next

'Copy Results into the Dataset:
For Each Match In Matches

    'Build an array for each row:
    Dim NewRow(ColumnCount - 1) As Object

    'Add the mRow Items:
    For x = 0 To MasterTableColumns.Count - 1
        NewRow(x) = Match.mRows.Item(x)
    Next

    'Add the srow Items:
    For x = 0 To SecondTableColumns.Count - 1
        Dim y As Integer = x + (MasterTableColumns.Count)
        NewRow(y) = Match.sRows.Item(x)
    Next

    'Add the array to dsResults as a Row:
    dsResults.Tables(qResultsName).Rows.Add(NewRow)

Next

提供给用户是否清理双打的选项：

If chkUnique.Checked = True Then
    ReMoveDuplicates(dsResults.Tables(qResultsName), ThePrimaryKey)
End If

如果需要的话删除重复项：

Private Sub ReMoveDuplicates(ByRef SkipTable As DataTable, _
                         ByRef TableKey As String)

    'Make sure that there's data to work with:
    If SkipTable Is Nothing Then Exit Sub
    If TableKey Is Nothing Then Exit Sub

    'Create an ArrayList of rows to delete:
    Dim DeleteRows As New ArrayList()

    'Fill the Array with Row Number of the items equal 
    'to the item above them:
    For x = 1 To SkipTable.Rows.Count - 1
        Dim RowOne As DataRow = SkipTable.Rows(x - 1)
        Dim RowTwo As DataRow = SkipTable.Rows(x)
        If RowTwo.Item(TableKey) = RowOne.Item(TableKey) Then
            DeleteRows.Add(x)
        End If
    Next

    'If there are no hits, exit this sub:
    If DeleteRows.Count < 1 Or DeleteRows Is Nothing Then
        Exit Sub
    End If

    'Otherwise, remove the rows based on the row count value:
    For x = 0 To DeleteRows.Count - 1

        'Start at the END and count backwards so the duplicate 
        'item's row count value doesn't change with each deleted row
        Dim KillRow As Integer = DeleteRows((DeleteRows.Count - 1) - x)

        'Delete the row:
        SkipTable.Rows(KillRow).Delete()

    Next
End Sub

然后清理所有剩余部分：

If Not chkRetainKeys.Checked = True Then 'Removes Forign Key
    dsResults.Tables(qResultsName).Columns.Remove(TheNewForignKey)
End If

'Clear Arrays
MasterTableColumns.Clear()
SecondTableColumns.Clear()

最终分析：
针对 2 个具有 4 列、65,535 行和一些双精度的文件进行此测试。处理时间，大约1秒。事实上，将字段加载到内存中比解析数据花费的时间更长。