UPDATE operation - Operation is not valid due to the current state of the object (VS2008 and Linq)

Question

Hi,

 

I'm using:

 

Visual Studio 2008

SQL 3.5 Compact Edition
Linq to SQL mappingUsing SQLMetal.

I have a root table called Artist that has a column called ContactLinkId

I have a second table called ContactLink with the primary key column ContactLinkId.

There is a foreign key on Artist(ContactLinkId) that references ContactLink(ContactLinkId). So a One -To-Many relationship that allows mulitiple contacts to be linked to an Artist.

 

When I attempt to update the ContactLinkId column on the Artist table, I get an "Operation is not valid due to the current state of the object" Which gets fired on: System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException();

What am I missing here? The ContactLinkId exists in ContactLink table so everything should be good, right?

 

Any help would greatly be appreciated!

 

Regards,

 

Aaron

 

Code Snippet

[Column(Storage="_ContactLinkId", DbType="UniqueIdentifier NOT NULL")]

public System.Guid ContactLinkId

{

get

{

return this._ContactLinkId;

}

set

{

if ((this._ContactLinkId != value))

{

if (this._ContactLink.HasLoadedOrAssignedValue)

{

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

}

this.OnContactLinkIdChanging(value);

this.SendPropertyChanging();

this._ContactLinkId = value;

this.SendPropertyChanged("ContactLinkId");

this.OnContactLinkIdChanged();

 

 

}

}

}

 

Answer 1

You should be setting the association property instead of the foreign key.

 

LINQ to SQL objects have both association properties (references or lists of other objects) and the key fields that are used to define these associations in the database.  That means there are at least two different properties on your object that determine this relationship.  If foriegn key fields and association properties are not kept in agreement, LINQ to SQL will throw this exception when you attempt to submit changes since it doesn't know which is the correct value, the one in the foreign key field or the one on the other side of the relationship.  The only time a foriegn key field is used exclusively as the value is when there is either no association property defined on the object using that field as a key or the association property was never assigned a value. 

 

The code-generated objects you are using will automatically keep the foreign keys in sync with any association property when you assign the association property a value.  Since they do this, they also know that any assignment to a foreign key field that actually changes its value is illegal if the corresponding association property has already been set.  That's why the generated property setter throws the exception directly, so you get the exception closer to where the bug is.

 

Answer 2

Thank you Matt for your response. However, being new to LINQ, I'm unfortunately not following your answer. In this scenario, how would I set the associated property?

Answer 3

artist.ContactLink = db.ContactLinks.Single(c => c.Id = someContactLinkId);

 

 

 

Answer 4

Wow... it that's easy. Thanks!

 

Just a note for newbs like me, I had to use.

Code Snippet

artist.ContactLink = db.ContactLinks.Single(c => c.Id == someContactLinkId);

 

 

Answer 5

Thanks for the great reply..It was realy helpfull

Answer 6

Hi Matt, so in LinqToSQL if I have a table with 20 foreign keys, are you saying that i have to do 20 lookups to the database using the following pattern?

table.Association = context.SomeEntittySet.Single(t => t.ID = someValue);

Isn't there a way to update the association without taking a database hit 20 times?  Similar to how you can update associations using EnityKey with the Entity Framework?

table.Association.EntityKey = new EntityKey("EntitySet", new KeyValuePair("ID", value));

thanks in advance.

Answer 7

I had the similar issue and this helped to solve it. Here the default image is the Foreign key entity with ImageId

 

oEntity.DefaultImage = gImageId.HasValue? GetDataContext().DefaultImages_Views.Single(item => item.ImageId == gImageId.Value): null;

 oEntity.ImageId = gImageId;

 //save he entity

Vijai