SQLFilter based on System Properties in Azure Service Bus Subscription

Question

I am working on setting up a topic/subscription service, and need help with the syntax of my SQLFilter so I can access BrokeredMessage Properties, specifically the To property. What is the correct syntax on a SQL Filter to access the System Properties of the message?

I have a working example using this tutorial where I can send and receive to my topic/subscriptions as desired: https://azure.microsoft.com/en-us/documentation/articles/service-bus-queues-topics-subscriptions/

However, now I want to setup SQL filters for each subscription based on the To property of the BrokeredMessage. The tutorial I followed mentions that it is possible "When a subscription is created, you can supply a filter expression that operates on the properties of the message, both the system properties (for example, Label) and custom application properties (for example, StoreName.)"

If I set a custom property, e.g. StoreName, like this:

message.Properties.Add("StoreName", "TestMe");

and set the subscription up with a SQL Filter like this:

namespaceManager.CreateSubscription("MyNSTestTopic", "TestSubscription", new SqlFilter("StoreName = 'TestMe'"));

The subscription filters as expected. However, if I try and use the BrokeredMessage objects To property (or label for that matter) as described in the article, I have been unable to get it to work. I've tried the following SQL Filters with no luck. What is the correct syntax to access the System Properties of the message?

Answer 1

From this article Topic Subscription Filters:

SQL Filters - A SqlFilter holds a SQL-like condition expression that is evaluated in the broker against the arriving messages' user-defined properties and system properties. All system properties (which are all properties explicitly listed on the BrokeredMessage class) must be prefixed with sys. in the condition expression. The SQL subset implements testing for existence of properties (EXISTS), testing for null-values (IS NULL), logical NOT/AND/OR, relational operators, numeric arithmetic, and simple text pattern matching with LIKE.

So in your case you need to create a SqlFilter on the sys.To property:

namespaceManager.CreateSubscription("MyNSTestTopic", "TestSubscription",
    new SqlFilter("sys.To = 'TestMe'"));