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.

LINQ to XML: Convert a List into a Set of XElements

I ran into the situation today where I had a List of integer Ids that I needed to turn into an XML string. I thought about using the XmlWriter class but felt it was a bit overkill as it was a very simple XML string I was trying to generate. My next thought was to just iterate through the Ids and generate the XML manually, something like the following:

List<int> ids = new List<int>() { 1, 2, 3, 4, 5 };

StringBuilder xml = new StringBuilder();
xml.Append("<Ids>");

foreach(int id in ids)
     xml.Append("<Id>" + id + "</Id>");

xml.Append("</Ids>");

Console.Write(xml.ToString());

// Result
// ------
// <Ids>
//     <Id>1</Id>
//     <Id>2</Id>
//     <Id>3</Id>
//     <Id>4</Id>
//     <Id>5</Id>
// </Ids>

While the above solution worked, I was wondering if there was something in the LINQ to XML classes that I could use to perform this operation in a more compact way. After some research it turns out there is a very elegant way of doing this. The single statement below creates a new XElement object with a single node named Ids and as that node’s content is passed a collection of XElement objects, one for each Id in the list.

List<int> ids = new List<int>() { 1, 2, 3, 4, 5 };

XElement xmlIds = new XElement("Ids", ids.Select(i => new XElement("Id", i)));

Console.Write(xmlIds.ToString());

// Result
// ------
// <Ids>
//     <Id>1</Id>
//     <Id>2</Id>
//     <Id>3</Id>
//     <Id>4</Id>
//     <Id>5</Id>
// </Ids>

This technique is very useful if you are using a LINQ to SQL DataContext which contains a stored procedure that accepts an XML string as a parameter as when calling that stored procedure from your DataContext, you are required to pass in an XElement not just an XML string.