Entity Sql for Many to Many Relationships

Question

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

Answer 1

You need to use some linq as follows:

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

This assumes that you used the entitiy structure to create a model for the data. The bill variable will store a collection of Bill objects associated with your product object.

Hope this helps.

Answer 2

Something like that

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

will create a string for each bit

Another variant:

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

A row will be created for each corresponding product (in this case, only one) and the second column in the row will include a nested result set containing accounts for this product.

Hope this helps

Alex