SQL Service Broker

[RoyChase]

First of all I thought that I would post this here rather than create an issue as it is more of a question not a bug.

I'm trying to read a SQL service broker queue using Lnq2Db. https://docs.microsoft.com/en-us/sql/t-sql/statements/receive-transact-sql?view=sql-server-ver15 and it works fine using an SQL string:

var sql = $@"WAITFOR (RECEIVE TOP(10) message_type_name MessageTypeName, CAST(message_body AS NVARCHAR(MAX)) AS MessageBody FROM {_setting.Queue}), TIMEOUT {_setting.WaitTimeout}";

var messages = await _db.QueryToArrayAsync<ServiceBrokerQueueItem.ServiceBrokerQueueItem>(sql, stoppingToken);

I'd prefer not to have sql strings anywhere in my code so thought about how I might achieve this with Linq2Db. I've got the cast working via an ExpresssionMethod but would like to achieve the rest of it.

The RECEIVE syntax is the same as a SELECT query so I'm looking for some way of replacing SELECT with RECEIVE.

The WAITFOR would need to be wrapped around the whole thing.

Is there any way that I might do this simply?

It's probably a single use case scenario so not worth spending loads of time upon it but if it can be done it would be great so any tips would be appreciated.

[sdanyliv]

Better SQL. A lot of work here.

[RoyChase]

I guessed that would be the answer 😊

Thanks