Updating entity with associate entity

Nov 18, 2009 at 3:05 PM

Hi there,

I don't know if i am wide of the mark here, so forgive me if i sound like a numpty. My point is based around updating.

I have a Product table, that has a ProductTypeId (int?). The productTypeId is a FK to a ProductType table

My Product Model has a few properties but also a property ProductType.

For whatever reason I need to change the Product's ProductTypeId to be something else. erm... from 1 to 2 - from "Jam" to "Jelly" Or was previously null and now gonna be something...

I get the orginal version of the entity, make the change to the productTypeId and then call the Update passing the entity.

Now, this doesn't do as i'd hope.

The update can fail in a number of ways.

If the productTypeId was not previously null it throws a ForeignKeyReferenceAlreadyHasValueException and to get around this you'd have to set the associated entity of the ProductType with the new related data "Jelly" prior to setting the productTypeId to 2. This seems like the wrong solution as I don't want to perform x number of reads to be able to update a set (depending on the number of associated entities you have and could be updating).

If the productTypeId was previously null, you manage to avoid the previous exception, but you reach this bit of code from the .generated.cs (In the " #region Associations") which I don't quite understand. But for me, as i haven't (and don't want to) set my ProductType entity prior to the update, seems to reset my new value i want to store (changes the 2 back to a null)

if (value != null) {
      _ProductTypeId = value.Id;
else {
     _ProductTypeId = default(int?);

Sorry I have gone around in circles for the last few hours with this, so i might not be 100% accruate with the details and the explaination may be a little cloudy.

So.... my question is, am i missing the point? shouldn't the model be able to just to update the fk without updating the associated entities too?

(not so magic) gumbo

Nov 18, 2009 at 3:22 PM

I think the issue is with your mapping. Presumably, you're mapping a LinqToSql entity to a custom Product model (a DTO type model) when you read and the reverse when you update.

So, when you read, your mapper takes the entity and the association to ProductType and populates the Product with the relevant properties and the child object of ProductType based on the L2S association.

On update, the reverse mapping of ProductType isn't 100% valid because when you think about it, you wouldn't do an update on a lookup table in sql as part of a related table. So, either you remove the ProductType property in your ProductModel, or you just remove that mapping before it calls the Update method.

Hopefully this should work ... (if I understand you correctly)


Nov 18, 2009 at 3:32 PM

The response time in this project is unreal.

Ok, I have been using the AutoMapper but you get an exception which on close investiagtion is throw due to the ForeignKeyReferenceAlreadyHasValueException.


Nov 19, 2009 at 11:59 AM
Edited Nov 19, 2009 at 12:05 PM

Linq to Sql is the culprit. When ever you create a linq to sql object it has an associated datacontext attached to it. This is to record all changes that have taken place to the object . Then when you commit the changes it is actually the datacontext being committed and not the object itself. This makes sense when you start thinking about child collections and adding/removing items from it. Something has to record the diff of the object graphs (thats why we love you linq to sql).

So when you create a linq to sql object with automapper it creates the object, but it does not have the datacontext associated with it. So I would load the object with the generic repository. Then use automapper to make the appropriate changes by mapping the DTO properties to the BO properties (look up the assembler pattern, as this is what we want).You can do this by passing in both the DTO and BO into automapper map function. Datacontext will record all of the changed properties. Then use the generic repository to update the object.

Nov 19, 2009 at 10:19 PM

Thanks Taliesins,

I'll have a play with this and get back to you.



Nov 20, 2009 at 10:08 AM

Alright, further info on this issue. It seems plenty of people are having issues with L2S and this particular problem.

We've identified where this happens now and maybe that can help us to get a good solution in place.

On the GenericRepository.Update method, it attempts to load the entity into the DataContext, change the values according to the incoming entity and then save the changes. When it tries to load the entity into the DataContext, it checks to see if the DataContext has something already loaded. In your case you have probably already loaded the entity (by doing a GetEntity elsewhere in the request) and L2S will not like any attempt to change a foreign key reference by the Id field because it already has a reference to the foreign key entity in memory (i.e. you can't change one without the other).

There may be a number of ways around this ...

1. Change the foreign key entity by creating a new one, removing the old reference and attaching this one.

2. Check out some of the Detach and Attach suggestions on other forums.

3. Try your hardest not to load the entity for update anywhere else before you do the update. :)

I'm looking into better ways of getting around this. I think we may be able to add a Detach solution to the GDL, but don't hold your breath on that.

Nov 20, 2009 at 10:41 AM

Further to this, I should also point out that if your DTO model has both ProductTypeId and ProductTypeModel properties (i.e. one an int value and one a reference to an equivalent dto model for the entity fk reference) then it will fail. If you remove the ProductTypeModel reference from the ProductModel then it will work. Also, if you remove the ProductTypeId and only update the ProductTypeModel then it will also work.

This may not suit what you need because you may actually need the FK DTO model for other purposes.