Entity Framework lookup table

Question

I have 3 tables

Brands:
BrandID int
BrandName varchar(30)

Products
ProdID int
ProdName varchar(30)

BrandToProd:
BrandID int => FK Brands.BrandID
ProdID int => FK Products.ProdID

After generating model from existing database EF omits BrandToProd table and creates Many-To-Many relationships between Brands and Products. I would like to have third entity with following fields:

BrandName varchar(30)
ProductsName varchar(30)

This will give me possibility to use scaffolding for this entity. Ideally, when I'll add new pair of Brand and Product, EF should check first if such Brand or Product already exist in database (to avoid duplicates), if no, add to corresponding tables and that add mapping to BrandToProd table. If Brand or Product already exist, EF should you existing BrandID/ProdID value when adding to BrandToProd table. Is there any idea how to do that?

Answer 1

Your BrandToProd table is a pure junction table, i.e. a table with only two foreign keys. It is an EF feature to model such tables into a many to many association without a class in the conceptual model.

The easiest way to include a pure junction table in the model as an entity class is

• add a dummy field to the database table temporarily
• generate the model
• delete the field from the database
• update the model from the database
• delete the property in the edmx diagram

An alternative way is to edit the edmx manually, but then you really need to know what you're doing. If you don't want to regenerate the model you could generate a second model and investigate the differences in both edmx files by a difference viewer.

However, I wonder if you need to do this. You seem to relate this to duplicate checking. But if you want to add a Brand or Product to the database you'll have to check for duplicates either way. If you want to add a new association (e.g. adding an existing Brand to Product.Brands) you don't have to check whether it exists. If it does, EF just ignores the "new" association.

Answer 2

As extra point to Gert's answer: when using surrogate keys, there is always the issue of duplicate management. Normally there is 1 or more fields that make a logical key. you can

• a)create a unique index on the Db. Db will complain when the constraint is violated
• b)Execute a logical duplicate check before attempting an insert.

Answer 3

I've ended up with just adding dummy ID field to my junction table, as I'm frequently changing DB schema (becsuse site development is in progress and I need from time to time update model from database) and don't want to each time remove/add dummy field to database. Another option I've used - SQL View on two tables and stored procedures mapped to corresponding actions (CRUD) in EF