LINQ to SQL - Select SUM with a WHERE

Question

I'm having trouble getting a Linq to Sql query to work. Basically, I want the sum of a column in a table for only rows matching a certain condition. A simplified version is this:

Orders = order that contains products OrderProducts = relational table containing products that are in an order Products = table containing information about products

I can get the total sum of the qty of products like this:

Dim totalQty = requests.Sum(Function(p) p.OrderProducts.Sum(Function(q) CType(q.ProductQty, Nullable(Of Integer))))

(Requests is a IQueryable of Orders)

But I also need to get the sum of qty where the actual product meets a certain condition. Something like...

        Dim totalQty = requests.Sum(Function(p) p.OrderProducts.Sum(Function(q) CType(q.ProductQty, Nullable(Of Integer))))

... WHERE p.OrderProducts.Product.ProductAttribute = true

How would I go about getting that query with the additional where clause to only get the sum of the qtys where the productAttribute = true ??? Thanks!

Answer 1

Dim totalQty = requests _
    .Sum(Function(p) p.OrderProducts _
        .Where(Function(q) q.Product.ProductAttribute = true) _
        .Sum(Function(q) CType(q.ProductQty, Nullable(Of Integer))))