vb.net - 如何从结果集中搜索结果集

Question

我在从结果集中搜索结果时遇到问题。

我根据客户 ID 搜索订单并存储在 rs 中，然后我想根据来自 rs 的订单 ID 搜索付款并存储在 rs2 中。

然后这个 rs2 将绑定(bind)在 datagridview 中。

但我发现一旦找到新数据，rs2 将继续重新分配以前的数据，所以最终结果总是显示一个结果，即找到的最后一个结果。我尝试使用静态 rs2，但它仍然无法正常工作。

希望有高手能提供解决办法。谢谢你。

Private Sub BindCard()
  Dim name As String = cboCreditType.Text
  Dim db As New ConcertDataContext()

  Dim rs = From o In db.orders Where (o.customer_id = MemberLogin.id)

  Dim allOrders = From id In rs Select id.order_id

  Dim rs2 = From o In db.payments
            Where (name = "All" Or o.creditType = name) And
                  allOrders.Contains(o.order_id)
            Select o.payment_id, o.total_payment, o.creditNumber, o.creditType, o.order_id

  dgv.DataSource = rs2 'Error occurs here'
End Sub

Answer 1

您已正确识别代码中的问题，解决方案非常简单。

删除循环，提取要处理的 orderid 列表，然后将该列表用作包含针对付款数据的条件

' First extract a list of all orders ID present in the orders selected
Dim allOrders = from id in rs select id.orderid

' Then use that list and select only the payments for those orders
rs2 = From o In db.payments 
        Where (name = "All" Or o.creditType = name) And 
        (allOrders.Contains(o.order_id))
              Select o.payment_id, o.total_payment, o.creditNumber, o.creditType, o.order_id

如果您不需要 rs 中的所有数据，则可以将初始查询替换为

Dim allOrders = From o In db.orders 
                  Where (o.customer_id = MemberLogin.id)
                  Select o.order_Id