Dapper.net with unknown quantity of array elements

Question

Dapper.net with unknown quantity of array elements

arrays dapper

Question

What I'm trying to do it have a filter object that is populated like so

var filter = new Filter
{
    ThingID = 1,
    Keywords = new[] { "op", "s" }
};

And then be able to build up the query like this:

var sb = new StringBuilder();
sb.AppendLine("select * from Stuff where 1=1");
if (filter.ThingID.HasValue)
{
    sb.AppendLine(" and ThingID = @ThingID");
}
if (filter.Keywords != null)
{
    for (int i = 0; i < filter.Keywords.Length; i++)
    {
        string keyword = filter.Keywords[i];
        if (!string.IsNullOrWhiteSpace(keyword))
        {
            sb.AppendLine(" and ( Model like '%' || @Keywords" + i + " || '%' )");
        }
    }
}
var sql = sb.ToString();
var results = Query<Stuff>(sql, filter).ToList();

This works fine if just the ThingID prop is populated, but as far as I can tell Dapper is not feeding the Keywords in as a parameter in any way. Is this possible with Dapper, or does it only work in the context of " where Keywords in @Keywords"?

feihtthief

View more on Stack Overflow

Marc Gravell · Accepted Answer · 6/28/2017 9:25:26 AM

Parameters need to match by name; you are adding parameters like @Keywords17, but there is no Keywords17 property for it to add. It doesn't interpret that as Keywords[17], if that is what you mean. There is some automatic expansion of flat arrays, but that is intended for expanding in @Keywords (although the expansion itself is not specific to in). There is not currently something that would help you automatically there; I would suggest DynamicPaameters instead:

var args = new DynamicParameters();
args.Add("ThingID", 1);
...
if (!string.IsNullOrWhiteSpace(keyword))
{
    sb.AppendLine(" and ( Model like '%' || @Keywords" + i + " || '%' )");
    args.Add("Keywords" + i, keyword);
}
...
var cmd = new CommandDefinition(sql, args, flags: CommandFlags.NoCache);
var results = Query<Stuff>(cmd).AsList();

note the subtle two changes at the end here - CommandFlags.NoCache will help avoid building lots of lookup entries for similar but different SQL (although you might choose to pay this to reduce per-item cost, up to you). The AsList instead of ToList avoids an extra list allocation.

Dapper.net with unknown quantity of array elements

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator