UserDequeueColumns

The transports that use relational databases let you add additional columns and use them in the de-queue statement.

To enable this feature, set this option when you create the queue:

 createQueue.Options.AdditionalColumnsOnMetaData = true;

With this option, any user-created columns go on the metadata table that is used to de-queue items.

Any columns that you add during queue creation are created on the metadata table, instead of the status table.

 createQueue.Options.AdditionalColumns.Add(new Column("DayOfWeek", ColumnTypes.Int, true, null));

When you add items to the queue through the producer, pass in a value for your column(s). These values are added to the queue record.

private static IAdditionalMessageData CreateData()
{
	var data = new AdditionalMessageData();
    data.AdditionalMetaData.Add(new AdditionalMetaData<int>("DayOfWeek", Convert.ToInt32(DateTime.Today.DayOfWeek)));
    return data;
}

When you create the consumer, you can specify a static where clause and parameters. You can also use a factory to set a dynamic where clause and parameters for the de-queue.

Note: The parameter type passed to SetUserParametersAndClause depends on the transport:

• SQL Server: Microsoft.Data.SqlClient.SqlParameter
• PostgreSQL: Npgsql.NpgsqlParameter
• SQLite: System.Data.SQLite.SQLiteParameter

The following example is SQL Server-specific:

var dayofWeek = int.Parse(ConfigurationManager.AppSettings.ReadSetting("UserDayOfWeek"));
log.Information($"Only processing items created on {((DayOfWeek)dayofWeek).ToString()}");
queue.Configuration.SetUserParametersAndClause(() => Parameters(dayofWeek), WhereClause);

private static string WhereClause()
{
	return "(DayOfWeek = @DayOfWeek)";
}

// SQL Server example - use NpgsqlParameter for PostgreSQL or SQLiteParameter for SQLite
private static List<SqlParameter> Parameters(int dayOfWeek)
{
	var list = new List<SqlParameter>();
	var userParam = new SqlParameter("@DayOfWeek", SqlDbType.Int)
	{
		Value = dayOfWeek
	};
	list.Add(userParam);
	return list;
}

This lets your consumers de-queue only the items that match criteria that the queue does not directly support. You could also disable the consumer: return (1=0) in the where clause. For instance, you can do this if you want processing to happen only between particular hours, but want your service to keep running at all times.