locked
Updating many-to-many relationships with databinding - ALMOST working RRS feed

  • Question

  • Background:
    I have two tables (Users and SecurityGroups) linked via a linking table.  Because the linking table only contains the IDs from those tables, Entity Framework hides this table and implements navigation properties on the User and SecurityGroup objects.  All of that is normal and for the most part I have no problems, but under certain situations I get an error when updating.

    I've sandboxed this problem, so I'm working with a simple Windows form consisting of a listbox, a checkedlistbox, and a button.  When a user is selected in the listbox, the checkedlistbox shows the security groups assigned to the user.  This works fine.

    If the selected user has no security groups assigned, I can select one or more, click save and everything updates as it should.

    If I then unselect one or more security groups and click save, again, it works as it should.

    I can even go back and reselect the same groups and click save again, and it will work.

    HOWEVER, if I clear a checkbox and then reselect the checkbox I get an error stating "The context is already tracking the relationship."

    As you can see in the code (I posted all the code from the form), when an item is unselected, I call DeleteLink.  When added I call AddLink.  The combination of DeleteLink followed by AddLink seems to be what is causing the problem. 

    I also run into a similar problem when changing users, but I think if I can understand the proper method to handle the first scenario I may be able to deal with the other.

    What is the proper way to implement what I'm trying to do?

    Thanks!
    Darvis

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.Services.Client;
    using TEST_DSBinding2.F2CDataServiceV2;
    using System.Collections.Specialized;

    namespace TEST_DSBinding2
    {
      public partial class Form1 : Form
      {
        private F2CEntities _ctx = new F2CEntities(new Uri("http://localhost:1116/F2CDataServiceV2.svc/"));
        private bool _loading;

        public Form1()
        {
          InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
          lstSecurityGroups.CheckOnClick = true;

          _loading = true;

          PopulateUserList();
          PopuplateSecurityGroupList();
          SelectSecurityGroupsForUser();

          _loading = false;
        }

        private void PopulateUserList()
        {
          var query = new DataServiceCollection<User>(from u in _ctx.Users.Expand("SecurityGroups")
                                                      select u);

          lstUsers.DataSource = query;
          lstUsers.DisplayMember = "Username";
          lstUsers.ValueMember = "UserID";
          lstUsers.SelectedIndex = 0;
        }

        private void PopuplateSecurityGroupList()
        {
          var query = new DataServiceCollection<SecurityGroup>(from g in _ctx.SecurityGroups
                                                               orderby g.Description
                                                               select g);

          lstSecurityGroups.DataSource = query;
          lstSecurityGroups.DisplayMember = "Description";
          lstSecurityGroups.ValueMember = "SecurityGroupID";
          lstSecurityGroups.SelectedIndex = -1;
        }

        private void SelectSecurityGroupsForUser()
        {
          var user = (User)lstUsers.SelectedItem;

          for (int i = 0; i < lstSecurityGroups.Items.Count; i++)
          {
            SecurityGroup sg = (SecurityGroup)lstSecurityGroups.Items[i];

            var x = (from s in _ctx.SecurityGroups where s.SecurityGroupID == sg.SecurityGroupID select s).First();
            lstSecurityGroups.SetItemChecked(i, (user.SecurityGroups.Contains(x)));
          }
        }

        private void lstUsers_SelectedIndexChanged(object sender, EventArgs e)
        {
          SelectSecurityGroupsForUser();
        }

        private void lstSecurityGroups_ItemCheck(object sender, ItemCheckEventArgs e)
        {
          if (_loading) return;

          var user = (User)lstUsers.SelectedItem;
          var securityGroup = (SecurityGroup)lstSecurityGroups.SelectedItem;

          if (e.NewValue == CheckState.Checked)
          {
            _ctx.AddLink(user, "SecurityGroups", securityGroup);
          }
          else
          {
            _ctx.DeleteLink(user, "SecurityGroups", securityGroup);
          }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
          _ctx.SaveChanges();
        }
      }
    }



    • Edited by DarvLom Tuesday, July 10, 2012 2:58 AM clarification
    Tuesday, July 10, 2012 2:18 AM

All replies

  • Can you try changing _ctx.DeleteLink to _ctx.DetachLink? That's what I had missed in the StackOverflow thread. (The answer is updated there.)

    HTH, Mark Stafford

    Wednesday, July 11, 2012 8:52 PM
  • DetachLink gets rid of the "The context is already tracking the relationship" error, but presents two other issues.

    1.  If I unselect a SecurityGroup and then call SaveChange, this doesn't result in a SQL Delete statement, so the relationship remains when I refresh the controls.

    2.  If I unselect a SecurityGroup, reselect it, and then call SaveChanges I get an error because it tries to add a duplicate row to the Users_SecurityGroups linking table.  I see in SQL Profiler that it attempts to execute an Insert statement but doesn't attempt a Delete. 

    Any suggestions?

    Wednesday, July 11, 2012 10:23 PM