Linq to Sql - Use sum in the where clause

Question

I'm trying to select orders that have either over or under 2000 products ordered in them, depending on other values. I need to select the information from the Orders table, but check this value in the OrdersProducts table, specifically the sum of OrdersProducts.ProductQty. I also need to do this using predicate builder, because of other requirements. So far, I have this, but it isn't returning the results correctly. Its using nested Lambda expressions, which I didn't know I could do but I tried it and it works, but its not returning correct results.

Dim getOrders = From d In db.Orders _
                Where d.Status = OrderStatus.Approved _
                Select d

' Then a for loop adding parameters via Predicatebuilder... 


If over2000 = True Then
    ' over 2000
    predicate1 = predicate1.And(Function(d) (d.OrderProducts.Sum(Function(c) c.ProductQty > 2000)))

Else
    ' under 2000
    predicate1 = predicate1.And(Function(d) (d.OrderProducts.Sum(Function(c) c.ProductQty < 2000)))

End If

basePredicate = basePredicate.Or(predicate1)


' End For loop

getOrders = getOrders.Where(basePredicate)

I removed some code for brevity but I think that gets the point across. How can I do this?? Thanks!

Answer 1

Try changing this:

(d.OrderProducts.Sum(Function(c) c.ProductQty > 2000))

to this:

(d.OrderProducts.Sum(Function(c) c.ProductQty) > 2000)

I haven't built this to test it, but it appears that it was currently trying to sum the results of a boolean comparison instead of summing the quantities and then comparing.