3

Say I have the following tables:

Essence, EssenceSet, and Essence2EssenceSet where Essence2EssenceSet holds just the IDs of the 1st 2 tables to form the M:M relationship.

In EF since Essence2EssenceSet has no other fields it is not exposed in the model. I find this makes it difficult to insert records into this table when I already have the 2 IDs needed to create the record but don't necessarily have the Essence and EssenceSet records loaded (Just their IDs)

Is there a way to tell EF to not model this way and always include the join table? Or am I missing an easier way to create these join table records?

user169867
  • 5,732
  • 10
  • 39
  • 56

2 Answers2

3

You can create M:N relation in EF without retrieving objects as well:

using (var context = new MyContext())
{
   var firstEntity = new FirstEntity { Id = firstId };
   var secondEntity = new SecondEntity { Id = secondId; }

   context.FirstEntities.Attach(firstEntity);
   context.SecondEntities.Attach(secondEntity);

   firstEntity.SecondEntities = new HashSet<SecondEntity>();
   firstEntity.SecondEntities.Add(secondEntity);

   context.SaveChanges();
}

Anyway exposing junction table as entity is possible but you will lose comfort of EF and fallback to SQL like approach:

  1. Delete M:N relation created by designer
  2. Add new entity
  3. Add two columns to the new entity representing foreign keys
  4. Map the new entity to junction table
  5. Add associations to related entities
  6. Set referential constraints for added relations
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • While this may work, I would be concerned about possible issues in the future as it may be taking advantage of behavior that shouldn't be relied on. Also, I'm assuming entities already exist with those IDs, and the system is assuming that no changes have been made to the object when you attach it, which is why this would work, but wouldn't you lose the existing relationships when you create a new hashset and add only the one item then persist it? – Brian Ball Mar 14 '11 at 23:56
  • No, you will not lose existing relations. – Ladislav Mrnka Mar 15 '11 at 05:45
0

Entity Framework is an ORM, and as such, when you work with it you aren't suppose to think of the database in terms of tables but instead in terms of objects. You shouldn't be inserting the identity into a table that holds the M2M relationship, but you should be loading one side of the relationship, which should expose a collection of the other side and add it to that collection. For a M2M, you may need to load the other side and do the same.

Also, I believe EF prefers all tables to have a single column PK (I could be wrong on this), but you may need to add a column to the M2M and designate it as a PK.

Brian Ball
  • 12,268
  • 3
  • 40
  • 51
  • Maybe, but the reality is they are tables and querying 2 objects from the DB in order to create a record I already have the info for seems wasteful. + adding another synth key as you suggest would in fact create the very JOIN table in the model you say I should be ignoring. – user169867 Mar 14 '11 at 20:55
  • You're right about adding the surrogate key, it probably shouldn't be done, but you would need to query for the objects in the database, it is one of the downsides of an ORM. Whenever you add another layer between your code and the DB, then it can sometimes create more work on the system. – Brian Ball Mar 14 '11 at 23:49