Linq to lambda sum作为where条件的Sql [英] Linq to Sql with lambda sum as a where condition

问题描述

我有以下查询

from p in _context.Products
where p.Purchases.Sum(item => item.CCAmount) > 0 && p.Purchases.Sum(item => item.CCAmount) > p.PayOuts.Sum((item => item.AmountPaid)
select p;

基本上，我正在尝试检索总购买金额大于0且总购买金额大于我们已付款金额的所有产品(我们代表他人出售产品并全额付款或部分付款).问题是，如果支出表中没有特定产品的条目，则该产品不会出现在结果列表中.但是，如果我在付款表中插入付款，则该产品将出现在产品列表中.几乎好像在一个空集合上使用sum不会像人们期望的那样评估，即为0.我在这里遗漏了什么吗?

Basically I am trying to retrieve all products that have a summed purchase amount greater than 0 and whose summed purchase amount is greater than the amount we have paid out (we are selling products on behalf of other people and pay them either in full or part payments). The problem is that if there are no entries in the payouts table for a particular product then that product does not appear in the resultant list. However if I insert a payout into the payouts table then that product will appear in the product list. Its almost as if using sum on an empty collection will not evaluate as one would expect i.e. as 0. Am I missing something here?

感谢您的帮助.

推荐答案

问题是，如果没有要累加的记录，SQL中的SUM返回null.

The problem is that SUM in SQL returns null if there are no records to sum.

您需要作弊一点.

尝试:

((int?)p.PayOuts.Sum(item => item.AmountPaid)).GetValueOrDefault()

或以一些不同的方式编写

or written in a little bit different way

((int?)p.PayOuts.Sum(item => item.AmountPaid) ?? 0)

这篇关于Linq to lambda sum作为where条件的Sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！