Remove() does not work on a many-to-many relationship (ASP.NET, lambda expressions)

Question

I am working with C# and ASP.net (4.0). I am trying to remove a record in my InterestsProfiles table using lambda expressions. The table has only two columns: profile id and interest id, and these are foreign keys to the Profiles table (id) and the Interests table (id).

So I managed to add to the Interests profiles table with the following code (the arguments to the function are string profileID, string name (of the interest)):

var interest = 
    context.a1Interests.Where(i => i.Interest.ToLower() == name.ToLower()).First();
if (interest == null)
  throw new HttpResponseException(HttpStatusCode.NotFound);

// Grab the profile
a1Profile profile = context.a1Profiles.Find(_id);
// Create a new profile that will be modified
a1Profile newProfile = profile;
if (profile == null)
  throw new HttpResponseException(HttpStatusCode.NotFound);

// Associate the interest with this profile
newProfile.a1Interests.Add(interest);

// Replace the old profile with the new one and save the changes
context.Entry(profile).CurrentValues.SetValues(newProfile);
context.SaveChanges();

I thought I could just do the opposite for removal, using .Remove(), but it does not work. The function returns the correct object and the status is 200/OK, but the entry in InterestsProfiles itself is not deleted.

newProfile.a1Interests.Remove(interest);

// Replace the old profile with the new one and save the changes
context.Entry(profile).CurrentValues.SetValues(newProfile);
context.SaveChanges();

The creation script for the table is this:

CREATE TABLE a1InterestsProfiles(
[Profile] [int] NOT NULL,
[Interest] [int] NOT NULL,
CONSTRAINT [PK_a1InterestsProfiles] PRIMARY KEY CLUSTERED 
    ([Profile] ASC, [Interest] ASC)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
ON [PRIMARY]) ON [PRIMARY]
GO 

-- Foreign key - Profiles
ALTER TABLE a1InterestsProfiles
ADD CONSTRAINT [FK_a1InterestsProfiles_Profiles] 
FOREIGN KEY ([Profile]) REFERENCES a1Profiles([ID])
GO
ALTER TABLE a1InterestsProfiles CHECK CONSTRAINT [FK_a1InterestsProfiles_Profiles]
GO

-- Foreign key - Interests
ALTER TABLE a1InterestsProfiles
ADD CONSTRAINT [FK_a1InterestsProfiles_Interests]
FOREIGN KEY ([Interest]) REFERENCES a1Interests ([ID])
GO
ALTER TABLE a1InterestsProfiles CHECK CONSTRAINT [FK_a1InterestsProfiles_Interests]
GO

Please help. I thought this would be really straightforward.

Answer 1

You need to .Attach() and entity when editing it;

to add

a1Profile profile = context.a1Profiles.Find(_id);
a1Profile.a1Interests.Add(interest);
context.SaveChanges();

to remove

newProfile.a1Interests.Remove(interest);
context.a1Profiles.Attach(newProfile);
context.SaveChanges();

Also worth looking at EntityState.Modified