Sql Query to Linq To Sql

Question

Hi,

I have some difficulties when it comes to multi-table query in Linq to Sql. I have 3 tables:

• Product
• Row (with Fk-ProductId)
• Price (with Fk-RowId)

I'd like to retrieve the min(Price) for a product. I was able to get the right Sql query, but now I need to 'translate' this query into Linq To Sql. Can you please help me ?

Here the Sql query:

SELECT Min(cp.Price) 
FROM Products p, Rows r, ConstantPrices cp 
WHERE p.ProductId = r.ProductId AND 
      r.RowId = cp.RowId AND 
      p.ProductId = XXX;

Answer 1

var productQuery = from p in myContext
               where p.id == someId
               select (double?) p.Row.Price;
var minPrice = productQuery.Min();

Its important the nullable in there, otherwise it would fail if there are no products or rows or prices in the system.

Its not about translating queries, its about taking advantage of what linq2sql gives you.

Answer 2

Here's a solution:

decimal? min = (from p in db.Products
               join r in db.Rows on p.ProductId equals r.ProductId
               join cp in db.ConstantPrices on r.RowId equals cp.RowId
               where p.ProductId == 1
               select cp.Price).Min();