Entity Sql for a Many to Many relationship

2019-07-15 19:20发布

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?

2条回答
迷人小祖宗
2楼-- · 2019-07-15 19:44

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.

查看更多
欢心
3楼-- · 2019-07-15 19:50

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

查看更多
登录 后发表回答