LINQ to SQL and the XML datatype

Today i have done some stuff with LINQ to SQL and the xml data type from SQL Server 2005. I had some problem finding information about this and spent some hours figuring out how the insert/update/delete worked. Post a comment if you find something I’ve done wrong. This is mostly for my own reference.

Get data

var q = (from ec in Business.PostCalc. ExtraCompensation.Descendants(“compensation”)select new { id = ec.Attribute(“id”).Value,  code = ec.Element(“code”).Value,  amount = ec.Element(“amount”).Value,  description = ec.Element(“description”).Value }).ToList(); lwExtraCompensation.DataSource = q;lwExtraCompensation.DataBind();

Delete data

You should of course run SubmitChanges and so on in some method. I do this way so that we can save everything at once later.

XElement ec = Business.PostCalc.ExtraCompensation;XElement newExtraComp= new XElement(ec); newExtraComp.DescendantsAndSelf(“compensation”).Where(x => x.Attribute(“id”).Value.ToString() == e.CommandArgument.ToString()).First().Remove(); Business.PostCalc.ExtraCompensation = newExtraComp; txtDescription.Text = “”;txtAmount.Text = “”;txtCode.Text = “”; PostCalculationChanged(this, new EventArgs());

Update data

XElement ec = Business.PostCalc.ExtraCompensation; XElement newExtraCompensation = new XElement(ec); newExtraCompensation.Add(new XElement(“compensation”,new XElement(“description”, txtDescription.Text),new XElement(“amount”, txtAmount.Text),new XElement(“code”, txtCode.Text),new XAttribute(“id”, Guid.NewGuid()))); Business.PostCalc.ExtraCompensation = newExtraCompensation; txtDescription.Text = “”;txtAmount.Text = “”;txtCode.Text = “”; PostCalculationChanged(this, new EventArgs());