c# - Linq-to-SQL With XML Database Fields -- Why does this work?

Question

Some background: I have an database that I want to use linq-to-sql to update through a C# application. One of the columns in this table has an XML datatype.

Every other column in that table (that isn't of an XML datatype) updates perfectly fine, but when I went to make changes to the XML field, the program executes (seemingly) correctly, but the field always retains its original value after I run SubmitChanges().

I looked around the internet and found a few posts on Microsoft Connect diagnosing similar problems and finally stumbled on the solution here:

To force XML field update this won't do:

XElement tmp = MyLinqObject.XmlField;
MyLinqObject.XmlField = null;
MyLinqObject.XmlField = tmp;

Instead of that to force LINQ to update XML column assign a cloned object:

MyLinqObject.XmlField = new XElement (MyLinqObject.XmlField);

I can confirm that this does indeed seem to work, but I'm not exactly sure why. My only guess is that the XmlField Property has some sort of unique identifier on the heap and that by making a clone, you've assigned it a new unique identifier. When Linq then generates the query, it doesn't even attempt to see if the field has been updated, since it has a new id, it simply write the value to the database. But, I'm simply speculating and hope that someone else can provide a better understanding of what is going on behind the scenes.

EDIT: To address Jon's post, the reason for the issue (as it is explained on the MS Connect site) is that "the XML field does not update because Linq-to-SQL doesn't handle the XElement.Changed event".

For my implementation, the code that works ends up looking something like this:

MyXElementProperty.SetElementValue("Author", author);

MyXElementProperty = new XElement(MyXElementProperty);

For reference (to anyone else that finds this question), the following also works:

MyXElementProperty = new XElement(MyXElementProperty);

MyXElementProperty.SetElementValue("Author", author);

Answer 1

When you make it a new XElement, you're making a different object. It's possible that to detect "staleness" it's using reference equality, which would obviously treat this as a new value.

At what point are you actually making changes to the element? I would expect LINQ to SQL to have a cached copy of the original value, and then compare that with the new value. If it's taking that "cached copy" by just copying the reference, then whatever you do to that object, it will always think the two are equal. If instead you create a new element, and then change that, then the old object will still have the old value, so the comparison will understand that you've made changes. Does that make sense?