A tricky one to solve, if the message is stored on the Service Broker as a binary object and its data type is changed in Entity Framework.
To add the stored procedure to the Entity Framework model, right-click on the model diagram and select ‘Update Model from Database…‘.
The stored procedure in this case moves a message selected in MVC Index view from one Service Broker queue to another – from a duplicates queue to an error message queue.
The base code I’ve copied from the Delete controller from the MVC 5 template, since the action is performs on the first match with the entry selected in the Index view. Here ‘queuing_order‘ is set as the ID:
db.DuplicatesQueueViews.First(d => d.queuing_order == id);
In my previous posts, I showed that my MVC project does not perform a transformation on the message pulled from Service Broker, but instead the data type is changed from varchar to string in the Entity Framework model. This means that message_body must be converted back to a character array before it’s passed to the stored procedure:
var originalmessage = duplicatesQueueView.message_body;
var binarymessage = Encoding.Default.GetBytes(originalmessage.ToCharArray());
Declare the message body with the current data type as a variable, then use
Encoding.Default.GetBytes() to convert the message to a character array. To use
Encoding.Default(), the System.Text assembly reference must be added to the .cs file.