UPDATE operation - Operation is not valid due to the current state of the object

Question

I have two tables that are linked by a foreign key reference, let's call them RX and program. I have this method that tries to change the program id in the RX table, which is an FKR to the program id field in the program table. Whenever I try to do this, I get an

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

Which gets fired on:

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

The offending code snippet and the database field definition are below. 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

Its a LinqToSql thing, what you are doing would work fine in EF and possibly other ORMs. You have to set the property that represents the object to the a new object rather than just updating the property that maps to the foreign key field in the database.

Presuming you have a property called Program then do this instead:

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

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