How do I tell Dapper to use varchar for a list of params in a "WHERE" clause that uses "IN"?

Question

I found this explanation and solution for using Dapper to search a VARCHAR field using a string as input:

Query<Thing>("select * from Thing where Name = @Name", new {Name = 
new DbString { Value = "abcde", IsFixedLength = true, Length = 10, IsAnsi = true });

Source: Dapper and varchars

But is there a way to adapt this to do the DbString conversion for every item in a list (using an IN clause)?

The query I am trying to run looks like this:

Query<IndexRec>("SELECT * FROM T_INDEX WHERE CallId IN @callIds",
new { callIds = model.LogEntries.Select(x => x.Id) });

Unfortunately, this query runs slowly because:

1. model.LogEntries contains around 300 items.
2. T_INDEX.CallId is a VARCHAR(30) field.
3. As I understand, Dapper uses NVarchar with strings in a WHERE clause by default.
4. This causes an implicit conversion of every row in my table in SQL, which slows down the query significantly.

How can I tell Dapper to use ansi strings in my IN clause for this query?

Answer 1

You should be able to pass in a list of DbString items, for example:

var parameters = model.LogEntries
    .Select(x => new DbString 
    {
        Value = x.Id, 
        IsAnsi = true
    });

Query<IndexRec>("SELECT * FROM T_INDEX WHERE CallId IN @callIds", new { callIds = parameters })