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.

▲

valiant55 8 hours ago | parent | next [-]

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

▲

wvenable 8 hours ago | parent | prev | next [-]

It's the optimizer caching the query plan as a parameterized query. It's not re-planning the index lookup on every execution.

▲

SigmundA 8 hours ago | parent [-]

The parameter type is part of the cache identity, nvarchar and varchar would have two cache entries with possibly different plans.

	▲	8 hours ago \| parent [-]
		[deleted]

▲

beart 8 hours ago | parent | prev [-]

How do you safely convert a 2 byte character to a 1 byte character?

	▲	jiggawatts 8 hours ago \| parent [-]
		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.