LINQ to SQL associations - how to change the value of associated field

Question

I have 2 classes with a LINQ association between them i.e.:

Table1:       Table2:
ID            ID
Name          Description
              ForiegnID

The association here is between Table1.ID -> Table2.ForiegnID

I need to be able to change the value of Table2.ForiegnID, however I can't and think it is because of the association (as when I remove it, it works).

Therefore, does anyone know how I can change the value of the associated field Table2.ForiegnID?

Thanks.

Answer 1

You wanna to associate with another record in table1 or change table1.id? if it's option 1, you need to remove that association and set a new one. If option 2, check you db and see if update cascade yes enabled for this fk and than get record and change value of id.

Answer 2

Check out the designer.cs file. This is the key's property

 [Column(Storage="_ParentKey", DbType="Int")]
 public System.Nullable<int> ParentKey
 {
  get
  {
   return this._ParentKey;
  }
  set
  {
   if ((this._ParentKey != value))
   {
    //This code is added by the association
    if (this._Parent.HasLoadedOrAssignedValue)
    {
     throw new System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException();
    }
    //This code is present regardless of association
    this.OnParentKeyChanging(value);
    this.SendPropertyChanging();
    this._ParentKey = value;
    this.SendPropertyChanged("ParentKey");
    this.OnServiceAddrIDChanged();
   }



  }
 }

And this is the associations property.

 [Association(Name="Parent_Child", Storage="_Parent", ThisKey="ParentKey", IsForeignKey=true, DeleteRule="CASCADE")]
 public Parent Parent
 {
  get
  {
   return this._Parent.Entity;
  }
  set
  {
   Parent previousValue = this._Parent.Entity;
   if (((previousValue != value) 
      || (this._Parent.HasLoadedOrAssignedValue == false)))
   {
    this.SendPropertyChanging();
    if ((previousValue != null))
    {
     this._Parent.Entity = null;
     previousValue.Exemptions.Remove(this);
    }
    this._Parent.Entity = value;
    if ((value != null))
    {
     value.Exemptions.Add(this);
     this._ParentKey = value.ParentKey;
    }
    else
    {
     this._ParentKey = default(Nullable<int>);
    }
    this.SendPropertyChanged("Parent");
   }
  }
 }

It's best to assign changes through the association instead of the key. That way, you don't have to worry about whether the parent is loaded.

Answer 3

Table1:       Table2:
ID            ID
Name          Description
              ForeignID

With this :

Table2.ForeignID = 2

you receive an error..........

Example :

You can change ForeignID field in Table 2 whit this :

   Table2 table = dataContext.Table2.single(d => d.ID == Id)

   table.Table1 = dataContext.Table1.single(d => d.ID == newId);

Where the variable newId is the id of the record in Table 2 that would you like associate whit the record in Table 1

Answer 4

What Zote wrote is correct. I have a blog post about this problem @ http://blog.frozengraphics.be/post/2010/01/13/Updating-foreign-key-with-LINQ.aspx

Answer 5

Hi, I have a problem with LINQ to SQL and C#, I try to change the Association Properties but this are atributes, how can i do? For example, just for one WebMethod I want to set the Table1-Table2 Association Child Property Access as Internal. Thanks!