ForeignKeyReferenceAlreadyHasValueException when creating Lookups using LINQ to SQL

Question

Hi to everyone... I am new to C# and .NET and I just started to study LINQ to SQL, I like it. But.. I found this one very annoying thing about it. Implementing lookUps is very complex because of the "ForeignKeyReferenceAlreadyHasValueException"! There is just NO simple straight-forward way of doing it! I noticed, if I delete all associations between LINQ entities, the "ForeignKeyReferenceAlreadyHasValueException" problem is no more! I am planing to develop small WinForms database applications, with no more then 100 tables...

So my question is:

What do I lose/risk if I use LINQ2SQL but delete all the associations between LINQ entities and keep relations in database ?

Answer 1

What you lose is, of course, the relationships. Normally LINQ to SQL will automatically populate useful collections that abstract the relationship, for example:

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

You would then have to write it the more roundabout way, e.g.

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

Answer 2

Basically you'll lose querying support, lazy-loading, IntelliSense, etc. For example, you won't have things like this if you delete the association between Users and Questions:

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

The point of LINQ is to provide you with all the constructs necessary to implement a relational database model within your C# code enabling you to query that model. If you delete all the associations/relationships LINQ to SQL loses it's purpose.

Regarding the Exception you got:

If the association property is assigned a value you cannot change the foreign key field anymore, you must change the relationship by changing the association 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. By Matt Warren (LINQ to SQL architect)

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

LINQ to SQL ForeignKeyReferenceAlreadyHasValueException error

For LookUps and LINQ, do this:

LINQ, Lookups, ForeignKeyReferenceAlreadyHasValueException

Use this code for example when binding a combobox:

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