Delete record in Linq to Sql
- by Anders Svensson
I have Linq2Sql classes User, Page, and UserPage (from a junction table), i.e. a many-to-many relationship. I'm using a gridview to show all Users, with a dropdownlist in each row to show the Pages visited by each user.
Now I want to be able to delete records through the gridview, so I have added a delete button in the gridview by setting "Enable deleting" on it. Then I tried to use the RowDeleting event to specify how to delete the records since it doesn't work by default. And because its a relationship I know I need to delete the related records in the junction table before deleting the user record itself, so I added this in the RowDeleting event:
protected void GridView2_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int id = (int)((DataKey)GridView2.DataKeys[e.RowIndex]).Value;
UserPageDBDataContext context = new UserPageDBDataContext();
var userPages = from userPage in context.UserPages
where userPage.User.UserID == id
select userPage;
foreach (var userPage in userPages)
context.UserPages.DeleteOnSubmit(userPage);
context.SubmitChanges();
var user = context.Users.Single(u => u.UserID == id);
context.Users.DeleteOnSubmit(user);
context.SubmitChanges();
}
This actually seems to delete records, because the record with the id in question does indeed disappear, but strangely, a new record seems to be added at the end...!
So, say I have 3 records in the gridview:
1 Jack stackoverflow.com
2 Betty stackoverflow.com/questions
3 Joe stackoverflow.com/whatever
Now, if I try to delete user 1 (Jack), record number 1 will indeed disappear in the gridview, but the same record will appear at the end with a new id:
2 Jack stackoverflow.com
3 Betty stackoverflow.com/questions
4 Joe stackoverflow.com/whatever
I have tried searching on how to delete records using Linq, and I believe I'm doing exacly as the examples I have read (e.g. the second example here: http://msdn.microsoft.com/en-us/library/Bb386925%28v=VS.100%29.aspx). I have read that you can also set cascade delete on the relationship in the database, but I wanted to do it this way in code, as your supposed to be able to. So what am I doing wrong?
