Inserting records into an XML table with a unique key

Question

Hi,

Maybe I'm just spoiled by SQL, but is there some cool LINQ way to insert a new record into an "XML table" and have the indexed ID update automatically (or at least semi-automatically)?

Here's what I mean by "XML table":

<myElements>
    <myElement>
     <id>1</id>
     <value>blah</value>
    </myElement>
    <myElement>
     <id>3</id>
     <value>blah</value>
    </myElement>
    <myElement>
     <id>4</id>
     <value>blah</value>
    </myElement>
    <myElement>
     <id>8</id>
     <value>blah</value>
    </myElement>
    <myElement>
     <id>9</id>
     <value>blah</value>
    </myElement>
</myElements>

In this case, the table is "myElements" and the records are the "myElement" elements (i.e., the children of myElements).

The "id" element is defined as a key in the schema, so it acts more or less as a "primary key", just like it would in SQL.

So, my question is, how do I insert a new "myElement" where the "value" element is "whatever" and the "id" element is automatically set to the next available id (in this example, it would be max(id) + 1 = 10).

I have my XML database stored in an XDocument, so I'd like a solution that uses LINQ and/or methods of XElement.

Thanks!

Answer 1

Well, since all my tables have an element called "id", I was able to write a fairly generic GetNextAvailableId element. So, here's how my insert routine would work for the "myElement" table.

public virtual int InsertMyElement(string value)
{
    int id = GetNextAvailableId("myElement");
    TableDictionary["myElement"].Add(
        new XElement(_ns + "myElement",
            new XElement(_ns + "id", id),
            new XElement(_ns + "value", value)));
    return id;
}

public virtual int GetNextAvailableId(string tableName)
{
    IEnumerable<int> ids =
        from
            r in TableDictionary[tableName].Elements()
        select
            int.Parse(r.Element(_ns + "id").Value);
    return ids.Max() + 1;
}

I don't feel too great about this solution--it will break if I ever decide to call my ID something besides "id"--but it seems like it will work for now.

Comments welcome.