AddWithValue, Dapper and EF Core: the NVARCHAR parameter that slows your queries

A .NET string is Unicode. SQL Server has two kinds of text: NVARCHAR (Unicode) and VARCHAR (a code page). When your data access code sends a string to a query that compares it with a VARCHAR column, the parameter’s type decides whether SQL Server can use the column’s index. Most libraries default to NVARCHAR.

The result, explained in detail in implicit conversions: SQL Server converts the column, not the parameter, and can’t seek the index. On the demo database, looking up one order by its VARCHAR(20) order number took 607 logical reads with an NVARCHAR parameter and 6 with a VARCHAR one.

What each library actually sends

To find out, we ran the same lookup from each library against SQL Server 2022 (Microsoft.Data.SqlClient 5.2, Dapper 2.1, EF Core 8), then read the parameterized text SQL Server cached:

SELECT t.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE t.text LIKE '%OrderNumber%' AND t.text NOT LIKE '%dm_exec%';

The declaration at the start of each cached statement is what the library sent:

Code	Parameter SQL Server received
cmd.Parameters.AddWithValue("@n", "SO-0000042")	@n nvarchar(10)
cmd.Parameters.AddWithValue("@n", "SO-00042")	@n nvarchar(8)
cmd.Parameters.Add("@n", SqlDbType.VarChar, 20).Value = ...	@n varchar(20)
Dapper, new { n = "SO-0000042" }	@n nvarchar(4000)
Dapper, new { n = new DbString { Value = ..., IsAnsi = true, Length = 20 } }	@n varchar(20)
EF Core, string property with no column type configured, variable	@__num_0 nvarchar(4000)
EF Core, same, constant in the query	N'SO-0000042' (a Unicode literal)
EF Core, property with .IsUnicode(false).HasMaxLength(20)	@__num_0 varchar(20), or 'SO-0000042'

Two problems show in this table:

1. NVARCHAR everywhere by default. Against VARCHAR columns, each of those defaults causes an implicit conversion of the column.
2. AddWithValue sizes the parameter to the value. nvarchar(10) and nvarchar(8) are different declarations, so SQL Server caches a separate plan for each length of string you ever send. On a busy system that’s thousands of near-identical plans, compiled one by one and crowding the plan cache.

The fix, library by library

ADO.NET

Give every parameter an explicit type and size that match the column:

command.Parameters.Add("@OrderNumber", SqlDbType.VarChar, 20).Value = orderNumber;
command.Parameters.Add("@Name", SqlDbType.NVarChar, 100).Value = name;
command.Parameters.Add("@CustomerId", SqlDbType.Int).Value = customerId;

This fixes both problems: the type matches, and the size is constant, so there’s one plan. Avoid AddWithValue for strings, and for decimals and dates too, where it also guesses precision from the value.

Dapper

Wrap VARCHAR values in DbString:

var order = await connection.QuerySingleOrDefaultAsync<Order>(
    "SELECT OrderId, Total FROM dbo.Orders WHERE OrderNumber = @n",
    new { n = new DbString { Value = orderNumber, IsAnsi = true, Length = 20 } });

Dapper already sends a fixed NVARCHAR(4000) for plain strings, so it doesn’t pollute the plan cache, but the type is still wrong for VARCHAR columns. If all your text columns are VARCHAR, you can change the default once at startup: Dapper.SqlMapper.AddTypeMap(typeof(string), DbType.AnsiString);.

EF Core

Tell the model which columns are not Unicode. Per property:

modelBuilder.Entity<Order>()
    .Property(o => o.OrderNumber)
    .IsUnicode(false)
    .HasMaxLength(20);

Or with attributes: [Unicode(false)] and [MaxLength(20)], or [Column(TypeName = "varchar(20)")]. If you scaffold the model from an existing database (dotnet ef dbcontext scaffold), EF Core writes these for you. The problem appears in hand-written models and code-first models that were never told.

For a database where nearly every string column is VARCHAR, set it as a convention:

protected override void ConfigureConventions(ModelConfigurationBuilder builder)
{
    builder.Properties<string>().AreUnicode(false);
}

Or change the column

If the column can contain text in several scripts (customer names, addresses), NVARCHAR is the right column type, and the NVARCHAR parameters are then correct. The rule is only that the parameter matches the column. Changing a column’s type on a large table is a migration with its own costs; changing the parameter type in code is usually the cheaper fix.

How to find it in your system

Search the plan cache for the conversion warning, as shown in implicit conversions: queries whose plans contain PlanAffectingConvert with ConvertIssue="Seek Plan", sorted by their total reads. For plan cache pollution from AddWithValue, look for many cached plans that share a query_hash:

SELECT TOP (10) qs.query_hash, COUNT(*) AS cached_plans, MIN(t.text) AS example_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
GROUP BY qs.query_hash
HAVING COUNT(*) > 1
ORDER BY cached_plans DESC;

The same query text with many plans, differing only in the declared lengths of their parameters, is the AddWithValue signature.

When the procedures your app calls are profiled with SpDev Pro, an NVARCHAR parameter against a VARCHAR column shows up as an implicit conversion finding on the exact line, with the column’s name and the type the parameter should have. With SpDev’s attach mode, you can also step through the procedure while your application calls it, and see the parameter types your code really sent.