updating a foreign key column in sql from C# using linq

Question

I am making a windows form app program in C#, using Linq. I am trying to update a foreign key column, but I keep on getting the error of:

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

The message box of the error points to the line:

throw new System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException();

I have used the following method to try update:

//Updates a Product
public void UpdateProduct(int productID, string productName, int categoryID, int supplierID, bool priceType, decimal costPrice, decimal retailPrice, bool inStock)
{
    Product product = (from p in db.Products
                       where p.ProductId == productID
                       select p).Single();

    product.Name = productName;
    product.CategoryId = categoryID;
    product.SupplierId = supplierID;
    product.PriceType = priceType;
    product.CostPrice = costPrice;
    product.RetailPrice = retailPrice;
    product.Stock = inStock;

    db.SubmitChanges();
}

What is the problem? Thank you in advance!

No. The offending line is product.CategoryId = categoryID;(foreign key column in table) product.SupplierId = supplierID;(foreign key column in table) — Jane Cohen, Apr 24 '18 at 20:32

score 0 · Answer 1 · answered Apr 24 '18 at 20:33

0

may be related to these Linq to SQL ForeignKeyReferenceAlreadyHasValueException and How to change the value of associated field

Sorry,I don't have enough reputation to write a comment that's why posting this as answer

answered Apr 24 '18 at 20:33

spartans bmk

371
1
5
9

score 0 · Answer 2 · answered Apr 24 '18 at 21:54

The error occurs if the navigation properties have already been loaded with values that do not match with the new ID you are trying to set.

Instead of setting the ID values, try setting the navigation properties, like this:

public void UpdateProduct(int productID, string productName, int categoryID, int supplierID, bool priceType, decimal costPrice, decimal retailPrice, bool inStock)
{
    Product product = (from p in db.Products
                       where p.ProductId == productID
                       select p).Single();

    Category cat = (from c in db.Categories
                    where c.CategoryId == categoryID
                    select c).FirstOrDefault();

    Supplier sup = (from s in db.Suppliers
                    where s.SupplierID == supplierID
                    select s).FirstOrDefault();

    product.Name = productName;
    product.Category = cat;
    product.Supplier = sup;
    product.PriceType = priceType;
    product.CostPrice = costPrice;
    product.RetailPrice = retailPrice;
    product.Stock = inStock;

    db.SubmitChanges();
}

updating a foreign key column in sql from C# using linq

2 Answers2