Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLFilter based on System Properties in Azure Service Bus Subscription

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?

BrokeredMessage message = new BrokeredMessage();
message.To = "TestMe";

namespaceManager.CreateSubscription("MyNSTestTopic", "TestSubscription", new SqlFilter("To = 'TestMe'"));
namespaceManager.CreateSubscription("MyNSTestTopic", "TestSubscription", new SqlFilter("Message.To= 'TestMe'"));
namespaceManager.CreateSubscription("MyNSTestTopic", "TestSubscription", new SqlFilter("MessageTo= 'TestMe'"));
namespaceManager.CreateSubscription("MyNSTestTopic", "TestSubscription", new SqlFilter("messageto= 'TestMe'"));
like image 201
Sam Ellis Avatar asked Sep 01 '16 23:09

Sam Ellis


People also ask

What is Azure Service Bus based on?

Microsoft Azure Service Bus is a Cloud-based Messaging as a Service (MaaS) Platform. It is a high-performance, real-time, and fault-tolerant service that transfers messages between your applications and databases securely.

What is subscription in Azure Service Bus?

A topic subscription resembles a virtual queue that receives copies of the messages that are sent to the topic. Consumers receive messages from a subscription identically to the way they receive messages from a queue.


1 Answers

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'"));
like image 186
Thomas Avatar answered Sep 30 '22 04:09

Thomas