ForeignKeyReferenceAlreadyHasValueException when creating Lookups using LINQ to SQL

Question

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

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 

Answer 2

You are going about making those relational changes the wrong way. If you have an Order object with a Customer object attached to it as a member, you can view Customer info like so:

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

However, if you need to change that Customer for that Order in the database, you can't just set the Order.CustomerID field to the new ID. THAT will throw the 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, once you pull down the Order.Customer from the database, you can't manually change the associated customer just by changing the foreign key on that row in the Orders table. You have to set the order.Customer member to be another live Customer object, then roll your change back to the database that way.

Answer 3

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);