Is it possible to create a correlated subquery in LINQ?

Question

I would like to create a LINQ query that returns the sum of all the values ​​for a given product number for a parent account and all child accounts.

I have a product table by account number, in which each row also contains a qty number and a parent account:

PartNumber   AccountNumber   ParentAccountNumber   Qty
----------   -------------   -------------------   ---
1000000390   27113           27173                  2
1000000390   27516           27173                  1
1000000390   00113           27173                  0
1000000390   27748           27173                  5

SELECT * FROM Inventory
WHERE ProductNumber='1000000390' 
AND ParentAccountNumber=(SELECT TOP 1 parentaccountnumber FROM Inventory 
WHERE accountnumber='27748')

is this possible in pure LINQ syntax? Should I use extension method syntax instead?

Thanks, -Keith

Answer 1

from item in Inventory
where item.ProductNumber == 1000000390
where item.ParentAccountNumber == (from subitem in Inventory 
                                   where subitem.AccountNumber == 27748
                                   select subitem.ParentAccountNumber).First()
select item

Something like that?

You can replace

  subitem.AccountNumber == 27748

from

   subitem.AccountNumber == item.AccountNumber

if that's what you wanted