Self modifying queries with macros - adamfoneil/Dapper.QX GitHub Wiki

Sometimes, the SQL of your query is more dynamic than you can represent with just properties. To get around this limitation, you can add macro placeholders within your query, then override the ResolveMacrosAsync method provide the dynamic SQL to your query along with a DynamicParameters object. Macros are any text surrounded by double brackets. For example, this query has a macro called huzzah:

public class WhateverQuery() : Query<WhateverType>
{
    public WhateverQuery() : base(
        @"SELECT *
        FROM [MagnificentTable]
        WHERE [Something]=@something <<huzzah>>
        ORDER BY [Yadda]")

    protected override async Task<(Dictionary<string, string> inserts, DynamicParameters parameters)> ResolveMacrosAsync(
        IDbConnection connection, IEnumerable<string> macros)
    {
        var insert = new Dictionary<string, string>()
        {
            ["<<huzzah>>"] = " AND [Field1] LIKE CONCAT('%', @field1, '%')"
        }

        var dp = new DynamicParameters();
        dp.Add("field1", "adventure awaits");
        return await Task.FromResult((insert, dp));
    }
}

This will resolve at runtime as:

SELECT *
FROM [MagnificentTable]
WHERE [Something]=@something AND [Field1] LIKE CONCAT('%', @field1, '%')
ORDER BY [Yadda]

This is a silly example that you wouldn't need a macro for in practice. But let's review what's happening:

• We have a regular query object with some base SQL. That SQL has a macro <<huzzah>>. If you did nothing else, the macro would be removed automatically at run time.
• To replace this placeholder with real SQL at runtime, we must override the ResolveMacrosAsync method. That method returns two things: a dictionary of strings mapping macros to SQL along with the values of any parameters included in that SQL using Dapper's built-in DynamicParameters object. This lets you have any number of macros within a single query. Your overridden method can provide any or all of the macros in your query. Any you don't return in your dictionary are removed from the query.