Dapper query with dynamic list of filters

Question

I have a c# mvc app using Dapper. There is a list table page which has several optional filters (as well as paging). A user can select (or not) any of several (about 8 right now but could grow) filters, each with a drop down for a from value and to value. So, for example, a user could select category "price" and filter from value "$100" to value "$200". However, I don't know how many categories the user is filtering on before hand and not all of the filter categories are the same type (some int, some decimal/double, some DateTime, though they all come in as string on FilterRange).

I'm trying to build a (relatively) simple yet sustainable Dapper query for this. So far I have this:

public List<PropertySale> GetSales(List<FilterRange> filterRanges, int skip = 0, int take = 0)
{
    var skipTake = " order by 1 ASC OFFSET @skip ROWS";
    if (take > 0)
        skipTake += " FETCH NEXT @take";

    var ranges = " WHERE 1 = 1 ";

    for(var i = 0; i < filterRanges.Count; i++)
    {
        ranges += " AND @filterRanges[i].columnName BETWEEN @filterRanges[i].fromValue AND @filterRanges[i].toValue ";
    }

    using (var conn = OpenConnection())
    {

        string query = @"Select * from  Sales " 
            + ranges
            + skipTake;

        return conn.Query<Sale>(query, new { filterRanges, skip, take }).AsList();
    }
}    

I Keep getting an error saying "... filterRanges cannot be used as a parameter value"

Is it possible to even do this in Dapper? All of the IEnumerable examples I see are where in _ which doesn't fit this situation. Any help is appreciated.

Answer 1

You can easily create that dynamic condition using DapperQueryBuilder:

using (var conn = OpenConnection())
{
    var query = conn.QueryBuilder($@"
        SELECT * 
        FROM Sales
        /**where**/
        order by 1 ASC 
        OFFSET {skip} ROWS FETCH NEXT {take}
    ");

    foreach (var filter in filterRanges)
        query.Where($@"{filter.ColumnName:raw} BETWEEN 
                       {filter.FromValue.Value} AND {filter.ToValue.Value}");

    return conn.Query<Sale>(query, new { filterRanges, skip, take }).AsList();
}

Or without the magic word /**where**/:

using (var conn = OpenConnection())
{
    var query = conn.QueryBuilder($@"
        SELECT * 
        FROM Sales
        WHERE 1=1
    ");

    foreach (var filter in filterRanges)
        query.Append($@"{filter.ColumnName:raw} BETWEEN 
                       {filter.FromValue.Value} AND {filter.ToValue.Value}");

    query.Append($"order by 1 ASC OFFSET {skip} ROWS FETCH NEXT {take}");

    return conn.Query<Sale>(query, new { filterRanges, skip, take }).AsList();
}

The output is fully parametrized SQL, even though it looks like we're doing plain string concatenation.

Disclaimer: I'm one of the authors of this library

Answer 2

You can use DynamicParameters class for generic fields.

                Dictionary<string, object> Filters = new Dictionary<string, object>();
                Filters.Add("UserName", "admin");
                Filters.Add("Email", "admin@admin.com");
                var builder = new SqlBuilder();
                var select = builder.AddTemplate("select * from SomeTable /**where**/");
                var parameter = new DynamicParameters();
                foreach (var filter in Filters)
                {
                    parameter.Add(filter.Key, filter.Value);
                    builder.Where($"{filter.Key} = @{filter.Key}");                        
                }


                var searchResult = appCon.Query<ApplicationUser>(select.RawSql, parameter);

Answer 3

I was able to find a solution for this. The key was to convert the List to a Dictionary. I created a private method:

private Dictionary<string, object> CreateParametersDictionary(List<FilterRange> filters, int skip = 0, int take = 0)
{
    var dict = new Dictionary<string, object>()
    {
        { "@skip", skip },
        { "@take", take },
    };

    for (var i = 0; i < filters.Count; i++)
    {
        dict.Add($"column_{i}", filters[i].Filter.Description);

        // some logic here which determines how you parse
        // I used a switch, not shown here for brevity
        dict.Add($"@fromVal_{i}", int.Parse(filters[i].FromValue.Value));
        dict.Add($"@toVal_{i}", int.Parse(filters[i].ToValue.Value));                         
    }
    return dict;
}

Then to build my query,

var ranges = " WHERE 1 = 1 ";
for(var i = 0; i < filterRanges.Count; i++)
    ranges += $" AND {filter[$"column_{i}"]} BETWEEN @fromVal_{i} AND @toVal_{i} ";

Special note: Be very careful here as the column name is not a parameter and you could open your self up to injection attacks (as @Popa noted in his answer). In my case those values come from an enum class and not from user in put so I am safe.

The rest is pretty straight forwared:

using (var conn = OpenConnection())
{
    string query = @"Select * from  Sales " 
        + ranges
        + skipTake;

    return conn.Query<Sale>(query, filter).AsList();
}

Answer 4

You can use a list of dynamic column values but you cannot do this also for the column name other than using string format which can cause a SQL injection.

You have to validate the column names from the list in order to be sure that they really exist before using them in a SQL query.

This is how you can use the list of filterRanges dynamically :

const string sqlTemplate = "SELECT /**select**/ FROM Sale /**where**/ /**orderby**/";

var sqlBuilder = new SqlBuilder();
var template = sqlBuilder.AddTemplate(sqlTemplate);

sqlBuilder.Select("*");

for (var i = 0; i < filterRanges.Count; i++)
{
    sqlBuilder.Where($"{filterRanges[i].ColumnName} = @columnValue", new { columnValue = filterRanges[i].FromValue });
}

using (var conn = OpenConnection())
{
    return conn.Query<Sale>(template.RawSql, template.Parameters).AsList();
}