Databases
StackOverflow2013
Postsdbo
POEntityFramework dbContext navigation properties causing duplicate joins
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEntityFramework dbContext navigation properties causing duplicate joins
    text
    copied!<p>I'm getting a lot of duplicate joins in the SQL generated from my DbContext object using the Entity Framework. Here's my basic structure</p> <p>CatalogTable( CatalogID int Primary Key )</p> <p>CategoryTable( CategoryID int PRIMARY KEY, CatalogID int NOT NULL )</p> <p>DepartmentTable( DepartmentID int PRIMARY KEY, CatalogID int NOT NULL)</p> <p>Product( CatalogID int NOT NULL, CategoryID int NOT NULL, DepartmentID int NOT NULL )</p> <p>Quite simply, a product has to have a catalog, category and department. I've setup all of the navigation properties to reflect this. Yes, a 'product' could be associated to a catalog indirectly through either the category or the department. But because there are two possible paths, I chose to explicitly indicate that a product belonged to a catalog.</p> <p>So the problem becomes the EF keeps joining in these required relationships basically spitting out double joins for category and department. </p> <p>Model:</p> <pre><code> //catalogs modelBuilder.Entity&lt;Catalog&gt;().ToTable("Catalog.Catalogs"); modelBuilder.Entity&lt;Catalog&gt;().HasKey(k =&gt; k.CatalogID); //products modelBuilder.Entity&lt;CatalogItem&gt;().ToTable("Catalog.Products"); modelBuilder.Entity&lt;CatalogItem&gt;().HasKey(k =&gt; k.ProductID); modelBuilder.Entity&lt;CatalogItem&gt;().HasRequired(req =&gt; req.CatalogCategory).WithMany().Map(m =&gt; m.MapKey("CatalogCategoryID")); modelBuilder.Entity&lt;CatalogItem&gt;().HasRequired(req =&gt; req.CatalogDepartment).WithMany().Map(m =&gt; m.MapKey("CatalogDepartmentID")); modelBuilder.Entity&lt;CatalogItem&gt;().HasRequired(req =&gt; req.Catalog).WithMany().Map(m =&gt; m.MapKey("CatalogID")); //departments modelBuilder.Entity&lt;CatalogDepartment&gt;().ToTable("Catalog.Departments"); modelBuilder.Entity&lt;CatalogDepartment&gt;().HasKey(k =&gt; k.CatalogDepartmentID); modelBuilder.Entity&lt;CatalogDepartment&gt;().HasRequired(req =&gt; req.Catalog).WithMany().Map(m =&gt; m.MapKey("CatalogID")); //categories modelBuilder.Entity&lt;CatalogCategory&gt;().ToTable("Catalog.Categories"); modelBuilder.Entity&lt;CatalogCategory&gt;().HasKey(k =&gt; k.CatalogCategoryID); modelBuilder.Entity&lt;CatalogCategory&gt;().HasRequired(req =&gt; req.Catalog).WithMany().Map(m =&gt; m.MapKey("CatalogID")); </code></pre> <p>Linq:</p> <pre><code>var query = Products .Include(inc=&gt;inc.CatalogCategory) .Include(inc=&gt;inc.CatalogDepartment) .Include(inc=&gt;inc.Catalog); query.ToList(); </code></pre> <p>Generated Sql:</p> <pre><code>SELECT * FROM [Catalog].[Products] AS [Extent1] INNER JOIN [Catalog].[Categories] AS [Extent2] ON [Extent1].[CatalogCategoryID] = [Extent2].[CatalogCategoryID] LEFT OUTER JOIN [Catalog].[Categories] AS [Extent3] ON [Extent1].[CatalogCategoryID] = [Extent3].[CatalogCategoryID] LEFT OUTER JOIN [Catalog].[Departments] AS [Extent4] ON [Extent1].[CatalogDepartmentID] = [Extent4].[CatalogDepartmentID] LEFT OUTER JOIN [Catalog].[Catalogs] AS [Extent5] ON [Extent1].[CatalogID] = [Extent5].[CatalogID] </code></pre> <p>You can clearly see "Categories" being joined twice; once as an inner join and once as an outer join.</p> <p>Any ideas here how to cleanly (and accurately) establish the relationships but prevent the duplicate binding? Thanks!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload