Entity Sql for a Many to Many relationship

Consider two tables Bill and Product with a many to many relationship. How do you get all the bills for a particular product using Entity Sql?

From stackoverflow

• You need to use some linq like this;

  ...
  using (YourEntities ye = new YourEntities())
  {
     Product myProduct = ye.Product.First(p => p.ProductId = idParameter);
     var bills = myProduct.Bill.Load();       
  }
  ...
  

  This assumes that you have used the entitiy framework to build a model for you data. The bills variable will hold a collection of Bill objects that are related to your product object.

  Hope it helps.

• Something like this

  SELECT B FROM [Container].Products as P
  OUTER APPLY P.Bills AS B
  WHERE P.ProductID == 1
  

  will produce a row for each Bill

  Another option is something like this:

  SELECT P, (SELECT B FROM P.Bills)
  FROM [Container].Products AS P
  WHERE P.ProductID == 1
  

  Which will produce a row for each matching Product (in this case just one) and the second column in the row will include a nested result set containing the bills for that product.

  Hope this helps

  Alex

• SELECT VALUE b 
  FROM Bills AS b 
  WHERE b.Product.Id == 1