UPDATE - operation is invalid due to the current state of the object

Question

I have two tables that are linked by a foreign key link, let them be called RX and the program. I have this method that tries to change the program identifier in the RX table, which is the FKR for the program identifier field in the program table. Whenever I try to do this, I get

"Operation is not valid due to the current state of the object"

Which is fired:

System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException(); What am I doing wrong here?

The code snippet and the definition of the database field are given below. The code:

  public void ChangeProgram(int programId, DbDataContext dc) { //var programId = ddlPrograms.SelectedValue.ToInteger(0); if (programId > 0) { var referral = PrescriptionManager.GetReferral(dc, _view.RxID); if (referral != null && referral.AspnRx.ProgramID != programId) { try { referral.ProgramID = dc.PROGRAMs.Single(p => p.ProgramID == programId).ToString().ToInteger(1); } catch (ForeignKeyReferenceAlreadyHasValueException exception) { _view.ChangeProgramSuccess = false; } 

Database field definition:

  [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ProgramID", DbType="Int")] public System.Nullable<int> ProgramID { get { return this._ProgramID; } set { if ((this._ProgramID != value)) { if (this._PROGRAM.HasLoadedOrAssignedValue) { throw new System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException(); } this.OnProgramIDChanging(value); this.SendPropertyChanging(); this._ProgramID = value; this.SendPropertyChanged("ProgramID"); this.OnProgramIDChanged(); } } } 

Answer 1

LinqToSql's own thing, what you do works fine in EF and possibly in other ORMs. You should set the property that represents the object for the new object, and not just update the property that appears in the foreign key field in the database.

Assuming you have a property called Program , then do this instead:

 referral.Program = dc.PROGRAMs.Single(p => p.ProgramID == programId); 

Not really, but .ToString (). ToInteger (1) looks a bit dubious, it looks like you are taking an integer, converting it to a string, and then back to an integer.