Convert SQL to Linq select in where

Question

I have three table below:

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

Current, How to using LinQ query get TABLE_STORE_HOUSE.IdProduct witch condition TABLE_STORE_HOUSE.ProductNumber - Sum(TABLE_SELL.ProductNumberSell) > 0 and TABLE_STORE_HOUSE.TimeInput is smallest

Help me convert Sql to 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

Can you try this?

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 }

for top 1 you can use Take() function.