Select query in LINQ based on Foreign Table

I have 2 Tables , OrderDetails and Requests In my LINQ to SQL dbml file. OrderDetailsID is a foreign 开发者_C百科key in Requests Table.

I want to write the query to get the sum of UnitCost from OrderDetails based on OrderId. And If there is a row in Requests Table for each OrderDetailsID, and the Requests.Last.RequestType="Refund" I want to reduce the total refund amount from the main sum otherwise If there is no row based on OrderDetailsID, add to sum.

Here is the way I implement that. I am looking to prevent using "For each".

Any Solutions?

 iRefund = (From od1 In dc.OrderDetails _
            Where od1.OrderID =1  _
            Select od1.UnitCost).Sum

 Dim objOrderDetails = (From od1 In dc.OrderDetails _
                       Where od1.OrderID =1  _
                       Select od1)

 For Each OrderDetail As ORM.Entities.OrderDetail In objOrderDetails
     If Not OrderDetail.Requests Is Nothing Then
         IF OrderDetail.Requests.Last.RequestType="Refund" Then
                   iRefund -= OrderDetail.UnitCost
         End If
     End If  
  Next

---

If you want to use Linq instead of a for-each loop, you could use the ForEach extension method of List:

Dim objOrderDetails = (From od1 In dc.OrderDetails _
                       Where od1.OrderId = 1 _
                       Select od1).ToList()

objOrderDetails.ForEach(AddressOf DecrementAmount)

---

Private Sub DecrementAmount(ByVal d As OrderDetail)
    If d.Requests IsNot Nothing _
        AndAlso d.Requests.Count > 0 _
        AndAlso d.Requests.Last.RequestType = "Refund" Then
        iRefund -= d.UnitCost
    End If
End Sub

This assumes that iRefund is a module variable.