StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POEntity Framework 4.0 Many-Many relationship Using Non-Primary Key
text
Body
copied!<p>I am trying to build a generic repository using Entity Framework 4.0 using a legacy MS SQL database I have inherited. A pretty familiar scenario.</p> <p>I need to add category information to a fairly long list of existing items.</p> <p>The items can belong to several categories at the same time so I created an mapping table called <strong>CategoryMapping</strong></p> <p><img src="https://farm6.static.flickr.com/5264/5597417399_a529174281_b.jpg" alt="Database Diagram"></p> <p>Unfortunately <strong>SchemaDefinitionCode</strong> is not unique and cannot be made into a Foreign Key (FK) in the database.</p> <p>I have tried to add my own partial class to the DefinitionSchema entity but as it's not indexed, this has a severe performance hit. <em>Demo code for testing, I won't want to create a new context every time I load this</em>:</p> <pre><code>public partial class DefinitionSchema { private MyEntities context; public IQueryable<Category> Categories { get { context = new MyEntities(); var categories = context.Categories .Where(c => c.CategoryMappings .Where(m => m.SchemaDefinitionCode == this.SchemaDefinitionCode).Any()); return categories; } } } </code></pre> <p>I can then call a list of items like so:</p> <pre><code>var q = context.SchemaDefinitions .Where(s => s.Categories .Where(c => c.Name == category) .Any() ); </code></pre> <p>How can I link my tables and mapping in the most efficient manner without wiping out the existing database structure?</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