c# - The relationship between the two objects cannot be defined because they are attached to different ObjectContext objects

Question

I have 2 tables in SQL. One is called Training and one is called Consultants. Training is couple of fields like ID, Place, Time, Date and it also has ConsultantName which is foreign key and is connected with Consultants having ConsultantName, ConsultantFirstName etc.

During save of Training i save the training first and then pass the Training value as varTraining. I tried to attatch it to context as per some suggestions I found on stack but it doesn't seem to work:

Training Training = context.Trainings.First(p => p.TrainingID == varTraining.TrainingID);
                context.Trainings.Attach(Training);
                currentUczestnik.Training = Training;

Here's the code:

        using (var context = new EntityBazaCRM(Settings.sqlDataConnectionDetailsCRM))
        {
            Training Training = context.Trainings.First(p => p.TrainingID == varTraining.TrainingID);
            foreach (var currentUczestnik in listaUczestnikow)
            {
                context.Trainings.Attach(Training);
                currentUczestnik.Training = Training;
                if (context.TrainingUczestnicies.Any(t => t.TrainingUczestnicy1 == currentUczestnik.TrainingUczestnicy1))
                {
                    context.TrainingUczestnicies.Attach(currentUczestnik);
                    context.ObjectStateManager.ChangeObjectState(currentUczestnik, EntityState.Modified);
                }
                else
                {
                    // context.Detach(currentUczestnik.Consultant);
                    context.Consultants.Attach(currentUczestnik.Consultant);
                    context.TrainingUczestnicies.AddObject(currentUczestnik);
                    //context.TrainingUczestnicies.Attach(
                }
            }
            try
            {
                context.SaveChanges();
            }
            catch (Exception ex)
            {
                string excepion = ex.InnerException.ToString();
                MessageBox.Show(excepion);
            }

        }

Edit:

The full story looks like this.

There is this Table with Consultants. People are loaded with:

using (var context = new EntityBazaCRM(Settings.sqlDataConnectionDetailsCRM)) {       
    IQueryable<Konsultanci> listaKonsultantow = from k in context.Konsultancis select k;      
    objectListViewKonsultanci.SetObjects(listaKonsultantow); 
}

They are loaded into ObjectListview. Then when user clicks on that ObjectListView the following Consultants are "copied" into ObjectListView Participants.

        foreach (Konsultanci konsultant in objectListViewKonsultanci.SelectedObjects) {
            dodajUczestnikowSzkolen(konsultant);
        }

New object is created called SzkolenieUczestniczy (Participants) and Consultant is assigned to it as it's connected. Some more properties are added too.

    private void dodajUczestnikowSzkolen(Konsultanci konsultant) {
        SzkolenieUczestnicy nowyUczestnik = new SzkolenieUczestnicy();
        nowyUczestnik.Konsultanci = konsultant;

        //nowyUczestnik.SzkolenieUzytkownik = Settings.currentlyLoggedKonsultant.KonsultantNazwa;
        nowyUczestnik.SzkolenieUzytkownikData = DateTime.Now;
        //listaUczestnicy.Add(nowyUczestnik);
        objectListViewUczestnicy.AddObject(nowyUczestnik);
    }

Now users saves Training (some fields like Date, Time, Topic were attached to to currentSzkolenie (Training):

    private void sqlZapiszSzkolenie() {

        using (var context = new EntityBazaCRM(Settings.sqlDataConnectionDetailsCRM)) {
            if (context.Szkolenies.Any(t => t.SzkolenieID == currentSzkolenie.SzkolenieID)) {
                context.Szkolenies.Attach(currentSzkolenie);
                context.ObjectStateManager.ChangeObjectState(currentSzkolenie, EntityState.Modified);
            } else {
                context.Szkolenies.AddObject(currentSzkolenie);
            }


            context.SaveChanges();
            context.Detach(currentSzkolenie);

        }
    }

So after I saved Training and the Training is now in database I head off to save Participants. I take currentSzkolenie (global var for Training) and pass it as variable:

 private void sqlZapiszUczestnikow(ObjectListView listaUczestnikow, Szkolenie varSzkolenie) {


        foreach (SzkolenieUczestnicy currentUczestnik in listaUczestnikow.Objects) {
            using (var context = new EntityBazaCRM(Settings.sqlDataConnectionDetailsCRM)) {
                  Szkolenie szkolenie = context.Szkolenies.First(p => p.SzkolenieID == varSzkolenie.SzkolenieID);

                  context.Attach(szkolenie);
                  currentUczestnik.Szkolenie = szkolenie;
              //  currentUczestnik.Szkolenie.EntityKey = szkolenie.EntityKey;
                if (context.SzkolenieUczestnicies.Any(t => t.SzkolenieUczestnicy1 == currentUczestnik.SzkolenieUczestnicy1)) {
                    context.SzkolenieUczestnicies.Attach(currentUczestnik);
                    context.ObjectStateManager.ChangeObjectState(currentUczestnik, EntityState.Modified);
                } else {
                    // context.Detach(currentUczestnik.Konsultanci);
                    context.Konsultancis.Attach(currentUczestnik.Konsultanci);
                    context.SzkolenieUczestnicies.AddObject(currentUczestnik);

                    //context.SzkolenieUczestnicies.Attach(
                }

                try {
                    context.SaveChanges();
                } catch (Exception ex) {
                    string excepion = ex.InnerException.ToString();
                    MessageBox.Show(excepion);
                }

            }
        }
    }

And that's about it.. it throws error on currentUczestnik.Szkolenie = szkolenie;

Answer 1

I solved it with 2 solutions.

1. I have declared private EntityBazaCRM context = new EntityBazaCRM(Settings.sqlDataConnectionDetailsCRM); on top of my class and reused it all the time without putting it into using. Thanks to this action I didn't have to attach anything prior to saving or anything. I was just using same context and attached any foreign keys I wanted using currentUczestnik.Szkolenie = szkolenie; and currentUczestnik.Konsultanci = consultants;. It saved without problems.

2. Instead of attaching whole object like currentUczestnik.Szkolenie = szkolenie; I've added it thru EntityKey on SzkolenieReference and SzkolenieID.

   context.Attach(szkolenie);
   context.Szkolenies.Attach(szkolenie);
   currentUczestnik.SzkolenieReference.EntityKey = szkolenie.EntityKey;
   currentUczestnik.SzkolenieID = szkolenie.SzkolenieID;
   

3. Solution number 3 was to create new object based on on old object. The referenced Konsultanci and Szkolenia are kind of resynchronized (I think it's actually retaking them from db).

    using (var context = new EntityBazaCRM(Settings.sqlDataConnectionDetailsCRM)) {
                   if (currentUczestnik.SzkolenieUczestnicyID == 0) {
                       var nowy = new SzkolenieUczestnicy {
                           Konsultanci = context.Konsultancis.First(p => p.KonsultantNazwa == currentUczestnik.Konsultanci.KonsultantNazwa),
                           Szkolenie = context.Szkolenies.First(p => p.SzkolenieID == varSzkolenie.SzkolenieID),
                           SzkolenieUzytkownik = currentUczestnik.SzkolenieUzytkownik,
                           SzkolenieUzytkownikData = currentUczestnik.SzkolenieUzytkownikData,
                           UczestnikPotwierdzilUdzial = currentUczestnik.UczestnikPotwierdzilUdzial,
                           UczestnikPrzybyl = currentUczestnik.UczestnikPrzybyl
                       };
   
   
                       context.SzkolenieUczestnicies.AddObject(nowy);
                       context.SaveChanges();
                       listaDoPodmiany.Add(nowy);
   

4. Solution number 4 is what I ended up using. Basically it's null-ing any referenced objects (first temporary saving them to objects) and then using just their ID values before save. Then after save objects are reattached.

           foreach (SzkolenieUczestnicy uczestnik in listaUczestnikow.Objects) {
               using (var context = new EntityBazaCRM(Settings.sqlDataConnectionDetailsCRM)) {
                   if (uczestnik.SzkolenieUczestnicyID == 0) { // add new object
                       var konsultant = uczestnik.Konsultanci; 
                       uczestnik.Konsultanci = null; // null attached object and reuse it's ID later on for SAVE purposes
                       uczestnik.KonsultantNazwa = konsultant.KonsultantNazwa;
                       uczestnik.Szkolenie = null; // null attached object and reuse it's ID later on for SAVE purposes
                       uczestnik.SzkolenieID = szkolenie.SzkolenieID;                       
                       context.SzkolenieUczestnicies.AddObject(uczestnik);
                       context.SaveChanges();
                       context.Detach(uczestnik); // detatch to prevent Context problems
                       uczestnik.Szkolenie = szkolenie;// reassign for use in ObjectListView
                       uczestnik.Konsultanci = konsultant; // reassign for use in ObjectListView
                   } else { // modify exisinng object 
                       context.SzkolenieUczestnicies.Attach(uczestnik);
   
                      //context.ObjectStateManager.ChangeObjectState(uczestnik, EntityState.Modified);
                       context.SaveChanges();
                   }
               }
           }