CODEWITHSUNDEEP
sql-serverxmllinq-to-sql
THX-1138
THX-1138

Reputation: 21750

Select from table using XML column

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?

Upvotes: 0

Views: 259

Answers (3)

CedricB
CedricB

Reputation: 1167

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.

Upvotes: 1

Eric K Yung
Eric K Yung

Reputation: 1784

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);

Upvotes: 0

marc_s
marc_s

Reputation: 755227

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.

Upvotes: 1

Related Questions