Comment by jiggawatts
7 hours ago
This feels like a bug in the SQL query optimizer rather than Dapper.
It ought to be smart enough to convert a constant parameter to the target column type in a predicate constraint and then check for the availability of a covering index.
There's a data type precedence that it uses to determine which value should be casted[0]. Nvarchar is higher precedence, therefore the varchar value is "lifted" to an nvarchar value first. This wouldn't be an issue if the types were reversed.
0: https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-...
It's the optimizer caching the query plan as a parameterized query. It's not re-planning the index lookup on every execution.
The parameter type is part of the cache identity, nvarchar and varchar would have two cache entries with possibly different plans.
How do you safely convert a 2 byte character to a 1 byte character?
Easily! If it doesn't convert successfully because it includes characters outside of the range of the target codepage then the equality condition is necessarily false, and the engine should short-circuit and return an empty set.