1

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.

I need to add category information to a fairly long list of existing items.

The items can belong to several categories at the same time so I created an mapping table called CategoryMapping

Database Diagram

Unfortunately SchemaDefinitionCode is not unique and cannot be made into a Foreign Key (FK) in the database.

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. Demo code for testing, I won't want to create a new context every time I load this:

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;
        }
    }
}

I can then call a list of items like so:

var q = context.SchemaDefinitions
.Where(s => s.Categories
    .Where(c => c.Name == category)
    .Any()
 );

How can I link my tables and mapping in the most efficient manner without wiping out the existing database structure?

Community
  • 1
  • 1
Peter Samuel
  • 282
  • 1
  • 4
  • 11

1 Answers1

0

It can't work this way because EF doesn't support unique keys and SchemaDefinitionCode must be unique to form valid many-to-many relation with Category. If your SchemaDefinitionCode is not unique in DefinitionSchema table it can't be used as principal end in the relation with CatagoryMapping. If it is unique you can use SchemaDefinitionID instead because no more then one Id will have the same code value.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670