Sql Query to Linq To Sql

Question

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;

bruno conde · Accepted Answer · 2009-09-22 17:45:31Z

1

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();

edited Sep 22, 2009 at 17:45

answered Sep 22, 2009 at 17:34

bruno conde

48.3k15 gold badges103 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eglasius Over a year ago

Don't forget to make the field nullable, as I mentioned in my answer not doing so would throw an exception if there are no items.

eglasius · Accepted Answer · 2009-09-22 17:22:41Z

0

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.

edited Sep 22, 2009 at 17:22

answered Sep 22, 2009 at 17:07

eglasius

35.9k5 gold badges69 silver badges110 bronze badges

Collectives™ on Stack Overflow

Sql Query to Linq To Sql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related