ForeignKeyReferenceAlreadyHasValueException when creating Lookups using LINQ to SQL

Question

I'm new to C # and .NET, and I just started learning LINQ to SQL, I like it. But ... I found it very annoying. The implementation of lookUps is very complicated due to the "ForeignKeyReferenceAlreadyHasValueException"! There is just a simple easy way to do it! I noticed that if I remove all associations between LINQ objects, the "ForeignKeyReferenceAlreadyHasValueException" problem will no longer be! I plan to develop small WinForms database applications, not more than 100 tables ...

So my question is:

What should I lose / risk if I use LINQ2SQL, but remove all associations between LINQ objects and save the relationships in the database?

Answer 1

Basically you will lose query support, lazy loading, IntelliSense, etc. For example, you will not have such things if you remove the connection between Users and Questions

from u in context.Users where u.Questions.Count > 2 select u; 

The LINQ point should provide you with all the constructs needed to implement the relational database model in C # code, allowing you to query this model. If you remove all associations / relationships, LINQ to SQL loses purpose.

Regarding Exception , you got:

If an association property is assigned a value that you cannot change the foreign key field, you must change the relationship by changing the association property. For example, using Customer and Order from the Northwind Sample Database. If the "Customer" property is assigned a value when ordering, you can no longer manually change the order of the CustomerID field, since it must correspond to the PK client. You can, however, change the Client property directly by assigning it a new client example. This action will automatically update the CustomerID field to match. Matt Warren (LINQ to SQL Architect)

This is what you need to do to solve your problem:

LINQ to SQL Error ForeignKeyReferenceAlreadyHasValueException

For LookUps and LINQ, do the following:

LINQ, Lookups, ForeignKeyReferenceAlreadyHasValueException

Use this code, for example, when linking a drop-down list:

 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 

Answer 2

You are about to make these relational changes in the wrong way. If you have an Order object with a Customer object attached to it as a member, you can view customer information like this:

 var order = db.Orders.Single(x => x.ID == 40); var cusName = order.Customer.GetFullCustomerName(); 

However, if you need to change this Customer for this order in the database, you cannot just set a new identifier for the Order.CustomerID field. This will throw a ForeignKeyReferenceAlreadyHasValueException error.

 // wrong way... order.CustomerID = 45; // id of the new customer // BOOOOM goes the error here. // right way... order.Customer = db.Customer.Single(x => x.ID == 45); db.SubmitChanges(); 

In other words, as soon as you pull out the Order. A client from the database, you cannot manually change the associated client by simply changing the foreign key in this row in the Orders table. You must set the order.Customer member to be another living object of the Client, and then transfer your change back to the database in this way.

Answer 3

What you lose is, of course, a relationship. Typically, LINQ to SQL automatically populates useful collections that abstract relationships, for example:

 var query = db.Orders.Where(o => o.Products.Count() > n); ^^^^^^^^ // You would lose this 

Then you will need to write it in a more circular way, for example

 var query = db.Orders.Where(o => db.OrderProducts.Count(p => p.ProductId == o.ProductId) > n);