Dynamic WHERE clause for BETWEEN with Dapper SQL Builder

Question

I am trying to build dynamic WHERE clause with Dapper SQL builder. This works very well for all cases except BETWEEN. Have tried the below approach with BETWEEN (just like IN clause)

SqlBuilder sqlBuilder = new SqlBuilder();
var sqlTemplate = sqlBuilder.AddTemplate(@"Select * from tbl_example /**Where**/");
sqlBuilder.Where(@"Col BETWEEN @colparam", new { paramValue });
con.Query(sqlTemplate.RawSql, sqlTemplate.Parameters);

When using SQL Profiler, I observed that the query generated from dapper looks like

Select * from tbl_example Where Col BETWEEN (@colparam1,@colparam2)

NOTE:

1. paramValue is an array of Integer which i receive via API
2. I do understand that this can be written like Col BETWEEN @colparam1 AND @colparam2 or replacing BETWEEN with < and >

Answer 1

As you know, BETWEEN expects two parameters (@colparam1 AND @colparam2), but you're passing only a single one (@colparam).

Assuming that your array contains exactly the boundaries (min and max), try this:

sqlBuilder.Where(@"Col BETWEEN @val1 AND @val2", new { val1 = paramValue[0], val2 = paramValue[1] });