Dapper nvarchar Implicit Conversion Silently Kills SQL Server Index Performance

A production performance investigation documented by developer Kyle Griffith reveals that Dapper's default string-to-nvarchar(4000) mapping silently forces CONVERT_IMPLICIT on every row in SQL Server when the target column is declared as varchar, turning index seeks into full table scans. The bug is invisible in C# code, produces correct query results, and generates no errors — but caused a single simple query to average thousands of milliseconds of CPU per execution across hundreds of thousands of daily calls. The fix is a one-line change to use DbType.AnsiString and explicit column-sized DynamicParameters instead of anonymous object parameter passing.

Key Takeaways

• Root cause: Dapper maps C# `string` to `nvarchar(4000)` by default; when the column is `varchar`, SQL Server emits `CONVERT_IMPLICIT` forcing an index scan instead of an index seek — visible as `CONVERT_IMPLICIT(nvarchar(255), [col], 0)` in execution plans
• Fix: replace anonymous `new { productCode }` with `DynamicParameters` and `parameters.Add("productCode", value, DbType.AnsiString, size: 100)` — matching the column type and size eliminates the implicit conversion and restores index seek behavior
• Detection: Query Store SQL `WHERE qsqt.query_sql_text LIKE '%@%nvarchar(4000)%'` identifies affected queries; CONVERT_IMPLICIT warnings in actual execution plans confirm the issue; severity depends on collation — SQL_Latin1_General_CP1_CI_AS (default) causes full scans

Original source: ConsultWithGriff