LINQ to SQL and the XML datatype

Posted on 29 February 2008, 16:09, by Ola, under LINQ.

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());
Comment (RSS)  |  Trackback

3 Comments

  1. Alexandre Brisebois says:
    21 May 2008 at 14:55

    I’ve run into the same problem, I can’t seem to find a way to update an XML field. If you do come across it please give me a heads up.

    Best regards,
    Alexandre Brisebois

  2. Tim Heap says:
    05 May 2009 at 10:40

    I’m also having the same issue trying to update xml field using Linq.

  3. Yuriy says:
    09 November 2009 at 9:33

    Here is a good explanation on XML field update in LINQ:
    http://ms-dotnetframework.blogspot.com/2009/09/linq-to-sql-submitchanges-problem-with.html

Leave a Reply

»