Select from table using XML column

Question

I am creating a task-scheduler on SQL Server 2008.
I have a table that I use to store tasks. Each task is a task name (e.g. ImportFile) and arguments. I store arguments in XML column, since different tasks have different signatures.

Table is as follows:
Id:integer(PK) | operation:nvarchar | Arguments:xml

Before queuing a task, I often need to verify that given task hasn't been scheduled yet. The lookup is done based on both operation and args.

Question: Using Linq-to-Sql how can I check if given operation+args is present in the queue already?

I am looking for something like:

var isTaskScheduled = db.Tasks.Any(t => 
    t.Opearation == task.Operation &&
    t.Arguments == task.ArgumentsAsXElement);

(which doesn't work because SQL Server can't compare XML type)

Any alternative implementation suggestions?

marc_s · Accepted Answer · 2010-11-18 18:15:55Z

1

You might want to surface e.g. a string property that encapsultes your Arguments, or maybe it would be sufficient to have e.g. the length and a CRC of your Arguments as extra properties on your class:

public partial class Task
{ 
   public int ArgumentLength 
   { .... }

   public int ArgumentCRC
   { .... }
}

That way, if you can compare length (of your XML) and the CRC and they match, you can be pretty sure and safe to assume the two XML's are identical. Your check would then be something like:

var isTaskScheduled = 
    db.Tasks.Any(t => t.Operation == task.Operation &&
                      t.ArgumentLength == task.ArgumentLength &&
                      t.ArgumentCRC == task.ArgumentCRC);

or something like that.

answered Nov 18, 2010 at 18:15

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

THX-1138 Over a year ago

Yes, reasonable. But I have problem with pretty sure part. I was hoping there is a better solution. A variation of this approach is to use CRC to significantly narrow down result subset, and then (if more then one result is returned) do (expensive) XML comparison.

CedricB · Accepted Answer · 2010-11-20 07:27:57Z

This may be a stretch, but you could use a "Hashcode" when saving the data to the database, then query on the hashcode value at a later date / time.

This assumes that you have a class that represents your task entity and that you have overridden the GetHashCode method of said class.

Now, when you go to query the database to see if the task is in the scheduled queue, you simply query on the hashcode, thus avoiding the need to do any xml poking at query time.

var t1 = new Task{Operation="Run", Arguments="someXElement.value"}; var t2 = new Task{Operation="Run", Arguments="someXElement.value"};

in the code above t1 == t2 because you are overriding GetHashCode and computing the hash for Operation+Arguments.Value. if you store the hashcode in the db, then you can easily tell if you have an object in the DB that equals the hash code that you are checking for.

This may be similar to what marc_s was talking about.

Eric K Yung · Accepted Answer · 2010-11-18 18:20:17Z

0

You can write a class which implements IComparable:

public class XMLArgument : IComparable
{
    public XMLArgument(string argument)
    {

    }

    public int CompareTo(object obj)
    {
        ...
    }
}

var isTaskScheduled = db.Tasks.Any(t => 
    t.Opearation == task.Operation &&
    (new XMLArgument(t.Arguments)).CompareTo(new XMLArgument(task.ArgumentsAsXElement)) == 0);

answered Nov 18, 2010 at 18:20

Eric K Yung

1,78411 silver badges11 bronze badges

Collectives™ on Stack Overflow

Select from table using XML column

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related