Linq to Entities Many to Many Query (with key-only joining table)
-
Hi there, Using Northwind as an example where there is a many to many join between Employees and Territories, can someone tell me why the code below doesn't work as I expect please? In this example I want to access the Employees. I'm using Count as a quick test rather than binding to a listview. The joining table only has keys in it, no other fields - so the entity model doesn't show the joining table. Navigation properties are shown instead (sometimes with a 1 appended??). using(NorthWindEntities ctx = new NorthWindEntities()){ var query = ctx.Territories.First(c => c.TerritoryID == "01581"); Label1.Text = query.TerritoryDescription; Label2.Text = query.Employees.Count.ToString(); } This gives this result: Westboro 0 So I can see the general database access is working but we are unable to access the employees. I was thinking it was because the query had already run to get the description and then a second SQL query was needing to be forced. However, it still doesn't work even if I only query the Employees count. Can someone advise please? (I can get many to many queries to work when the joining table has extra fields and thus appears in the model. I have looked at another article: <a href="http://weblogs.asp.net/zeeshanhirani/archive/2008/08/21/many-to-many-mappings-in-entity-framework.aspx">http://weblogs.asp.net/zeeshanhirani/archive/2008/08/21/many-to-many-mappings-in-entity-framework.aspx</a>[<a href="http://weblogs.asp.net/zeeshanhirani/archive/2008/08/21/many-to-many-mappings-in-entity-framework.aspx" target="_blank" title="New Window">^</a>] Cheers!
-
Hi there, Using Northwind as an example where there is a many to many join between Employees and Territories, can someone tell me why the code below doesn't work as I expect please? In this example I want to access the Employees. I'm using Count as a quick test rather than binding to a listview. The joining table only has keys in it, no other fields - so the entity model doesn't show the joining table. Navigation properties are shown instead (sometimes with a 1 appended??). using(NorthWindEntities ctx = new NorthWindEntities()){ var query = ctx.Territories.First(c => c.TerritoryID == "01581"); Label1.Text = query.TerritoryDescription; Label2.Text = query.Employees.Count.ToString(); } This gives this result: Westboro 0 So I can see the general database access is working but we are unable to access the employees. I was thinking it was because the query had already run to get the description and then a second SQL query was needing to be forced. However, it still doesn't work even if I only query the Employees count. Can someone advise please? (I can get many to many queries to work when the joining table has extra fields and thus appears in the model. I have looked at another article: <a href="http://weblogs.asp.net/zeeshanhirani/archive/2008/08/21/many-to-many-mappings-in-entity-framework.aspx">http://weblogs.asp.net/zeeshanhirani/archive/2008/08/21/many-to-many-mappings-in-entity-framework.aspx</a>[<a href="http://weblogs.asp.net/zeeshanhirani/archive/2008/08/21/many-to-many-mappings-in-entity-framework.aspx" target="_blank" title="New Window">^</a>] Cheers!
Hi there, I have found the solution to this, along the lines of what I thought might be happening. You need to populate the context with the new items by calling Load(). So, in the example above it would be like this: using(NorthWindEntities ctx = new NorthWindEntities()){ var query = ctx.Territories.First(c => c.TerritoryID == "01581"); Label1.Text = query.TerritoryDescription; query.Employees1.Load(); <<<<<<<<<<<<<<<<<<<<<<<<<<<< hydrate the Employees using Load(); Label2.Text = query.Employees1.Count.ToString(); } Still not quite sure about the appended "1". Hope this helps someone.