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

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();
}