locked
ForeignKeyReferenceAlreadyHasValueException

    Question

  • Hi. Ok, here is my problem. I have a table PRODUCTS, table ORDERS and table ORDER_ROWS.

    PRODUCTS contains "ProductId" field (primary key)
    ORDER_ROWS contains "ProductId" (foreign key)

    Now, I've got like 20 tables at this moment in my database, all related to each other, and it all works great, for some weird reason except for those two I mentioned above.

    Whenever I try to change single order_rows's productId to some other (existing) productId, "ForeignKeyReferenceAlreadyHasValueException" is thrown.
    I was Googling to find a solution to my problem. Some say it's because both entity sets are loaded into memory (why would that should cause any problem I wonder, but nvm) and we try to change but one "ProductId" property.
    Other say not "ProductId" property of Order_Rows must be changed, instead entire Order_Rows must me passed.

    Now I've tried the second solution, but then Linq either tries to duplicate other already existing records in related tables (as he would try to insert it all over again), or it says I can't change "ProductId" property because it defines other underling objects :/

     

    I wanted to see if this is fault of my code, so I added a extra field to my ORDER_ROWS and linked it with other table. This one new field I could change without problems.

    Does anyone have any idea how I can solve this? Thx

    Friday, November 09, 2007 9:11 AM

Answers

  • If the association property is assigned a value you cannot change the foreign key field anymore, you must change the relationship by changing the assocition property.    For example, using Customer and Order from Northwind sample database.  If the 'Customer' property on an order is assigned a value you can no longer manually change the order's CustomerID field, since it must match the PK of the customer.  You can, however, change the 'Customer' property directly, assigning it a new customer instance.  This action will automatically update the CustomerID field to match.

     

     

    Monday, November 12, 2007 1:02 AM

All replies

  • If the association property is assigned a value you cannot change the foreign key field anymore, you must change the relationship by changing the assocition property.    For example, using Customer and Order from Northwind sample database.  If the 'Customer' property on an order is assigned a value you can no longer manually change the order's CustomerID field, since it must match the PK of the customer.  You can, however, change the 'Customer' property directly, assigning it a new customer instance.  This action will automatically update the CustomerID field to match.

     

     

    Monday, November 12, 2007 1:02 AM
  • That's exactly my problem. If I try to do it the way you described it, Linq tries to insert new values to related tables instead of updating the existing ones. Let me explain:

    //Create order bll with CurrentOrder Property
    OrderBll obll = new OrderBll(11);

    //Create debtor bll
    DebtorBll db = new DebtorBll();

    //Get new debtor
    Debtor d = db.GetDebtor(1000009);

    //Change debtor in order (it was 10000008)
    obll.CurrentOrder.Debtor = d;

    //Update in database (only db.submitchanges())
    obll.ChangeOrder();


    Linq creates a new debtor as copy of debtor 1000009 with a new ID (10000010). After that, my CurrentOrder has debtor 10000010 assigned instead of 1000009 it was given.

     
    Could you tell me what am I doing wrong here? I would greatly appreciate it if you showed me (in code) how it must be done right. Thank you very much up front.

    Tuesday, November 13, 2007 4:12 PM
  • Hi,

    I'm a little bit further...

    Code Block

    //Create order bll with CurrentOrder Property
    OrderBll obll = new OrderBll(11);

    //Change debtor in order (it was 10000008)
    obll.CurrentOrder.DebtorId = 1000009;

    //Update in database (only db.submitchanges())
    obll.ChangeOrder();


    This works fine!

    But.....

    Code Block

    //Create order bll with CurrentOrder Property
    OrderBll obll = new OrderBll(11);


    //Get firstname of the debtor of the current order

    String test = obll.CurrentOrder.Debtor.Firstname;


    //Change debtor in order (it was 1000008)
    obll.CurrentOrder.DebtorId = 1000009;

    //Update in database (only db.submitchanges())
    obll.ChangeOrder();


    This results in an ForeignKeyReferenceAlreadyHasValueException.

    How we're working:

    In the constructor of the Order form, we're creating a new OrderBll. In the constructor of the OrderBll, a new CurrentOrder property is created.

    After that, we're filling all the textboxes in the form with the obll.CurrentOrder. Then a user can change the textboxes/dropdowns and after that, we're changing the obll.CurrentOrder with the new values (obll.CurrentOrder.Date = txtDate.text, obll.CurrentOrder.Notice = txtNotice.text). After that, we do a db.SubmitChanges().

    All works fine, but when we're reading a value from a table, linked to our order table, we got that damned exception Sad.

    Can anyone help us?





    Wednesday, November 14, 2007 8:58 AM
  • Because you've followed the foreign key into the Users table you can't change that property because then whatever you'd set above would no longer be valid.

    How I fixed my problem was that instead of following the foreign key and getting the value that way I did another query to get it.

    String test = db.Users.Single(x => x.orderID == userID.
    DebtorId).Firstname;

    This way you've only taken the value, not followed the foreign key so you can change whatever you want. 

    If someone has a cleaner solution I'd definetly like to hear, this is just what worked for me.
    Wednesday, April 23, 2008 10:36 PM
  • Hi Jhilden ,i  fixed my problem like your solution,i am looking for a better solution.i think there will be a better solution.
    Tuesday, June 24, 2008 12:05 PM
  • In order to update the FK in the parent object you have to set the child object (property) in the parent object to the new child object - this will update the FK in the parent record for you.  The trick is that you *have* to use the same DataContext that you used to fetch the parent object to fetch the new child object.   For example:

    If I have a Widget and a WidgetMaterial table with the Widget having a FK linking a WidgetMaterial record to it.

    Dim dc As New DataContext()
    Dim wi As Widget = From w In dc.Widgets ...

    ...

    To set the FK in the Widget table to point to a new WidgetMaterial record we need to update the Widget.WidgetMaterial property to the desired new WidgetMaterial record.  So say we want to change the FK in Widget to point to the WidgetMaterial record with ID 5:
    (Using the same DataContext we used to fetch the Widget record originally!)

    Dim newWidgetMaterialRec as WidgetMaterial = (From wm In dc.WidgetMaterials Where wm.ID = 5).Single

    ' here is how we set the FK in Widget - by setting the entire child object to the new one we selected in the previous step.
    wi.WidgetMaterial = newWidgetMaterialRec
    dc.SubmitChanges()

    If you do not use the same data context then LINQ attempts to create a new record in the WidgetMaterial table and will throw an exception.  If you try to update the FK in the Widget object directly then you will get the ForeignKeyReferenceAlreadyHasValueException.

    Hope this helps!



    Thursday, October 02, 2008 4:11 PM
  •  Matt Warren - MSFT wrote:

    If the association property is assigned a value you cannot change the foreign key field anymore, you must change the relationship by changing the assocition property.    For example, using Customer and Order from Northwind sample database.  If the 'Customer' property on an order is assigned a value you can no longer manually change the order's CustomerID field, since it must match the PK of the customer.  You can, however, change the 'Customer' property directly, assigning it a new customer instance.  This action will automatically update the CustomerID field to match.

     


    When the foreign key is nullable, this problem will not occur. Never the less it is better to change the 'Customer' property to prevent it from happening.

    Friday, December 05, 2008 8:58 AM

  • If you're using a bound combo box to, for instance, assign a category to a product (an "Item" in this example), clear out any of the databinding propertiesin the properties window and then use code like this in the form load:


            With cboCategory 
                .DataSource = From Category In db.Categories Order By Category.Name Select Category 
                .DisplayMember = "Name" 
                '.ValueMember = "ID"    don't set value member: http://tinyurl.com/d9etoy 
                .DataBindings.Add(New Binding("SelectedItem", ItemBindingSource, "Category")) 
            End With 

    • Edited by M_o_n_t_y Friday, March 20, 2009 5:03 AM spelling
    Friday, March 20, 2009 5:02 AM
  • Friday, March 27, 2009 5:05 PM