How do you insert or update many to many tables in .net entity framework

Question

This seems like it should be quite obvious but something about the entity framework is confusing me and I cannot get this to work.

Quite simply, I have three tables where the Id values are identity columns: Users (userId, username) Categories (categoryId, categoryName) JoinTable (UserId, CategoryId) composite.

In the entities designer (this is .net 4.0), when I import these tables, as expected the join table does not appear but Users and Categories show a relationship. The following code:

var _context = new MyContext();
var myUser = new User();
myUser.UserName = "joe";

var myCategory = new Category();
myCategory.CategoryName = "friends";

_context.Users.AddObject(myUser);  
myUser.Categories.Add(myCategory);

var saved = _context.SaveChanges();

Returns an error of (though nothing was added to the database):

An item with the same key has already been added.

If I add the following before saving:

_context.Categories.AddObject(myCategory);
myCategory.Users.Add(myUser);

I get the same error and nothing saved to the db. If I save the myUser and myCategory object before trying to associate them, they both save, but the second save throws an error, with nothing added to the join table:

Cannot insert the value NULL into column 'UserId', table '...dbo.JoinTable'; column does not  allow nulls. INSERT fails. The statement has been terminated.

I'm clearly failing to understand how many to many relationships are inserted. What am I missing?

Answer 1

You do need to call SaveChanges() after adding User and Category entities to the database, and then set your association between them.

However, the real problem here is the second exception you listed. If you look at SqlProfiler or the ADO.NET profiler within the debugger, you will see that during the second SaveChanges call it looks something like this:

insert [dbo].[JoinTable]([UserId]) values (@0) select [CategoryId] from
[dbo].[JoinTable] where @@ROWCOUNT > 0 and [UserId] = @0 and [CategoryId] = scope_identity()

Obviously this won't work if you programmed your JoinTable correctly (composite PK on both columns).

If I look at the EntityModel store through Model Browser, it shows that the CategoryId column inside JoinTable does indeed have StoreGeneratedPattern set to Identity while UserId is set to None. Why EF did this during the generation phase when a composite PK was present is beyond me. I'll be posting a bug about this to MS, however in the mean time you can manually edit the edmx/ssdl file after generation to remove the Identity specifier. Find the StoreGeneratedPattern="Identity" string under the Property tag of the EntityType tag for your JoinTable and remove it:

Change:

<Property Name="CategoryId" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />

To:

<Property Name="CategoryId" Type="int" Nullable="false" />

Then when you run your code you will get a much better insert query (and no more exception!):

insert [dbo].[JoinTable]([UserId], [CategoryId]) values (@0, @1)

Answer 2

The way I have done this is to first generate a valid Category entity with the entity key.

Category myCategory = _context.Categories.First(i => i.CategoryID == categoryIDToUse);

Or you can try to create the entity as a stub to save the hit to the DB:

Category myCategory = new Category{CategoryID = categoryIDToUse };

Then add that entity to the entity set(CategorySet) on the ObjectContext using the AttachTo method(you may want to check if it is already attached). Then you can add the Category to your User entity using the Add method. Something like this:

myUser.Categories.Add(myCategory);

Call SaveChanges(). That has worked for me.