SQL Server susceptible to imlicit conversions

[commonquail]

With SQL Server, every helper that sports a WHERE predicate, when receiving some key property of type string, is liable to incur implicit conversion. string gets translated into nvarchar but if the database column type is either varchar or char(n) the column types will be mismatched, the column will be auto-converted, and performance tanks. Dealing with this idiosyncrasy in Dapper.Contrib appears to be only narrowly possible: there is no attribute with which to specify the database column type, no hook to override the type at render time, and no native way to cast the value in the generated predicate. There is zero native support for DbType, SqlDbType, or Dapper's DbString.

The best I can come up with is instructing GetDatabaseType to use a hypothetical implementation like

public class X : SqlServerAdapter
{
    private readonly IReadOnlyDictionary<string, string> dbTypeByName = new Dictionary<string, string>
    {
        { "foo", "varchar" },
    };

    public new void AppendColumnNameEqualsValue(StringBuilder sb, string columnName)
    {
        if (dbTypeByName.TryGetValue(columnName, out var dbType))
            sb.AppendFormat("[{0}] = cast(@{1} as {2})", columnName, columnName, dbType);
        else
            base.AppendColumnNameEqualsValue(sb, columnName);
    }
}

However, it pays to be aware that AppendColumnNameEqualsValue gets used both for the update column and the predicate, and even if that is desirable it lacks context and falls apart the moment two identically named columns have incompatible types.

The only practically feasible remedies I can see are changing the database column types, if possible, or avoiding the various predicate aware helpers entirely.