Removing messages from a Service Broker queue isn’t straightforward as deleting a database table row, because a message is chained into a conversation and Service Broker expects it to be received eventually instead of dropped.
Initially I tried loading the entire queue into memory as objects (which Entity Framework already does) and removing the selected object before dumping everything back into the queue. After running into problems with that, I decided on a much simpler solution that involved creating a database table (dbo.QueueDump here) where selected messages could be dumped and later deleted.
When creating this table, it’s a good idea to add a timestamp and primary key, along with the main data column for the message body.
CREATE TABLE [dbo].[QueueDump](
[Id] [int] IDENTITY(1,1) NOT NULL,
[message_body] [xml] NULL,
[dateCreated] [datetime] NULL CONSTRAINT [DF_QueueDump_dateCreated] DEFAULT (getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
My application already calls stored procedures for moving messages between queues, taking ‘conversation_handle‘ as the message identifier, so it’s just a matter of repurposing one of them to start a new conversation on a different queue and terminate the old conversation.
Next, in the application project, update the Entity Framework model to include the stored procedure (‘Update Model from Database…‘), or just add the following to the model’s DbContext class:
The controller for calling the stored procedure would look something like this:
And remember to modify the View layer also for the Delete function: