Write a SQL query with a subquery in .NET Core using Entity Framework that returns a list of items with varying number of returned child rows

I have this test SqlFiddle: http://sqlfiddle.com/#!18/05fce/1

The idea is that for each product (table webspiderproduct) it will return the lowest price from each shopId from the table webspiderproductprice.

So the returned data should look like this:

ProductId: 1, ProductName: "sample product1"
   shopId: 1, lowestPrice: 100
   shopId: 2, lowestPrice: 199
ProductId: 2, ProductName: "sample product 2"
   shopId: 1, lowestPrice: 99
   shopId: 2, lowestPrice: 119
   shopId: 3, lowestPrice: 19

I am using SQL Server.

In pseudocode should I write a

select * 
from webspiderproduct 

and then do a foreach through each row using linq and then add the child rows from webspiderproductprice or should I do a join query ?

Answer

Use the following code:

  select t1.ProductId
    ,t1.productname
    ,t2.shopId
    ,t2.price as lowestPrice
  from 
      webspiderproduct t1
  join 
      (select *,ROW_NUMBER() over(partition by webspiderproductid,shopid 
  order by price asc) as rn from webspiderproductprice) t2
  on t1.productid=t2.webspiderproductid
  where rn=1
  order by ProductId

Output: