将SQL转换为Linq在其中选择

Question

我有下面的三个表：

TABLE_PRODUCT (IdProduct, ProductName, ProductUnit) 
TABLE_STORE_HOUSE (IdContain, IdProduct, ProductNumber, TimeInput) 
TABLE_SELL (IdSell, IdContain, ProductNumberSell, TimeSell)

当前，如何使用linq查询获取 TABLE_STORE_HOUSE.IdProduct witch条件 TABLE_STORE_HOUSE.ProductNumber - Sum(TABLE_SELL.ProductNumberSell) > 0和 TABLE_STORE_HOUSE.TimeInput是最小的

帮助我将SQL转换为Linq ..................................

select top 1 IdContain 
from 
    TABLE_STORE_HOUSE 
where IdProduct = '6' 
      and 
        ProductNumber - (select sum(ProductNumber) 
                         from TABLE_SELL 
                         Where TABLE_SELL.IdContain = IdContain)> 0 
order by TimeInput desc;

Answer 1

您可以尝试吗？

from t in TABLE_STORE_HOUSEs 
let TSell =  (
                from s in TABLE_SELLs 
                where s.IdContain == t.IdContain
                orderby s.ProductNumber
                select new { 
                         s.ProductNumber
                        }
               )
where t.IdProduct == 6 && (t.ProductNumber - TSell.Sum(si => si.ProductNumber)) > 0
select new { t.IdContain }

对于顶部1，您可以使用Take()函数。