Linq to Sql-在where子句中使用sum [英] Linq to Sql - Use sum in the where clause

问题描述

我正在尝试选择订单数量超过或少于2000的订单，具体取决于其他值.我需要从Orders表中选择信息，但是要在OrdersProducts表中检查此值，尤其是OrdersProducts.ProductQty的总和.由于其他要求，我还需要使用谓词构建器执行此操作.到目前为止，我已经有了这个，但是它没有正确返回结果.它使用嵌套的Lambda表达式，虽然我不知道我能做些什么，但是我尝试了一下并且可以使用，但是没有返回正确的结果.

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!

推荐答案

尝试更改此内容:

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

对此:

(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.

这篇关于Linq to Sql-在where子句中使用sum的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！