LINQ to SQL: XElement Not Being Updated in the Database


If you are using LINQ to SQL to manage the interaction between your database and your application, you will notice that any columns you have defined as an XML SQL data type are represented using the XElement class in the generated classes. This makes working with XML data stored in a database very easy and convenient. But, today I ran into a problem while trying to update the XML stored in the database. My code followed the logic shown below.

// Get a report for the database
Report report = (from r in db.Reports
                 where r.Id == id
                 select r).First();

// The Report.Definition property is a representation of an XML column
// Get the first 'Group' element and add a new SubGroup child
XElement element = report.Definition.Elements("Group").First();
element.Add(new XElement("SubGroup", value));

// Commit the changes to the database
db.SubmitChanges();

The problem was that the changes made to the XML found in the Definition property were committed to the DataContext but not to the database.

After some research it appears that when SubmitChanges is called on the DataContext, it checks the entities it has retrieved to see if any changes were made. To do this, the DataContext must cache the original values it retrieved from the database. The problem with the code above is that when you access and change the report.Definition property directly, you are changing the value cached by the DataContext. Thus, when you call the SubmitChanges method and comparisons are made to the cached value, it appears nothing has changed as you edited the cached value. To solve this, do the following

// Get a report for the database
Report report = (from r in db.Reports
                 where r.Id == id
                 select r).First();

// The Report.Definition property is a representation of an XML column
// Get the first 'Group' element and add a new SubGroup child
XElement element = report.Definition.Elements("Group").First();
element.Add(new XElement("SubGroup", value));

// Force a reference inequality
report.Definition = new XElement(report.Definition);

// Commit the changes to the database
db.SubmitChanges();

Now when the DataContext checks to see if anything has changed, there will be a reference inequality between the original Report.Definition and the new XElement you assigned to Report.Definition. Unfortunately the LINQ to SQL classes don’t handle the XElement.Changed event and flag that something has changed.

Posted in LINQ, SQL. Tags: , . 1 Comment »

One Response to “LINQ to SQL: XElement Not Being Updated in the Database”

  1. steventrigg Says:

    Thanks for the article, exactly what I needed.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: