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"?
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.