One Line of EF Core Config Cut My Query’s Reads by 99%

Your EF Core query is correct. It returns the right row. The column has an index.

And SQL Server still reads the whole index to find one row.

The cause is one EF Core default that most C# developers never think about. In this post, I will show you the problem with a real demo, the one-line fix, and a checklist to find it in your own project.

TL;DR: EF Core sends C# strings as nvarchar by default. If your column is varchar, SQL Server converts the column and can scan the whole index. In my demo, one line of configuration, .IsUnicode(false), cut logical reads from 887 to 3.

The Setup

Here is a simple table. The Email column is VARCHAR, and it has an index.

CREATE TABLE dbo.Customers ( Id INT IDENTITY(1,1) PRIMARY KEY, Email VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, FullName VARCHAR(100) NOT NULL ); CREATE INDEX IX_Customers_Email ON dbo.Customers (Email); 

I filled it with 200,000 rows of test data.

And here is a normal EF Core entity with a normal query.

public class Customer { public int Id { get; set; } public string Email { get; set; } = ""; public string FullName { get; set; } = ""; } var id = await db.Customers .Where(c => c.Email == email) .Select(c => c.Id) .FirstOrDefaultAsync(); 

Nothing looks wrong. That is exactly the problem.

What EF Core Actually Sends

C# strings are Unicode. So unless you tell it otherwise, EF Core maps a string property to nvarchar and sends the parameter as nvarchar.

With sensitive data logging turned on in my demo, EF Core logged this:

Executed DbCommand (272ms) [Parameters=[@email='user150000@example.com' (Size = 4000)], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [c].[Id] FROM [Customers] AS [c] WHERE [c].[Email] = @email 

Size = 4000 with no DbType shown means a Unicode string: an NVARCHAR(4000) parameter.

The column is VARCHAR. The parameter is NVARCHAR. They do not match.

A small bonus detail from my first run: when the value was a C# const, EF Core did not send a parameter at all. It inlined the value into the SQL as N'user150000@example.com'. That N prefix makes it an NVARCHAR literal, so the result was exactly the same scan. Constant or variable, the default mapping produces the mismatch.

Why That Hurts the Index

When SQL Server compares two different types, it converts one of them. NVARCHAR has higher data type precedence than VARCHAR, so SQL Server converts the column, not the parameter.

Converting the column means SQL Server cannot simply jump to the matching value in the index. With a SQL collation like the one in my demo, it scans the index and converts every value as it goes.

Here are the actual execution plans. Query 1 uses an NVARCHAR parameter, like EF Core's default. Query 2 uses a VARCHAR parameter.

Look at the first plan closely, and two things stand out:

1. A yellow warning triangle on the SELECT operator.
2. The query cost: 100% of the batch, while the VARCHAR version costs 0%.

The warning is SQL Server's hint that something is wrong. To see exactly what, hover over the Index Scan:

Look at the Predicate at the bottom: CONVERT_IMPLICIT(nvarchar(100), [Email], 0) = [@nv]. SQL Server converted the Email column itself, on every row, and read 200,000 rows to return 1.

Now the seek:

One row read. No conversion.

One small note: with Windows collations, SQL Server can sometimes still do a seek after the conversion. It is less dramatic there, but the mismatch still adds work. The fix is the same either way.

The Numbers From My Demo

SQL Server read 200,000 rows to return one. Logical reads dropped from 887 to 3, a reduction of more than 99%, and the plan's share of the batch cost went from 100% to 0%. Running the same EF Core query 200 times took 3,408 ms before the fix and 130 ms after, about 26 times faster.

These numbers come from a demo table on my laptop, using SQL Server 2016 LocalDB (13.0.7037), .NET 10 and EF Core 10.0.15. A real production table can be much bigger, and the gap grows with it.

The One-Line Fix

Tell EF Core the column is not Unicode.

modelBuilder.Entity<Customer>() .Property(c => c.Email) .IsUnicode(false) .HasMaxLength(100); 

After this change, EF Core logged:

Executed DbCommand (8ms) [Parameters=[@email='user150000@example.com' (Size = 100) (DbType = AnsiString)], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [c].[Id] FROM [Customers] AS [c] WHERE [c].[Email] = @email 

DbType = AnsiString means VARCHAR, and Size = 100 matches the column. The types match, and SQL Server goes back to an index seek.

HasMaxLength(100) is optional for the seek, but it makes the parameter size match the column too, which keeps your query plans consistent.

Other ways to say the same thing

Option A: Data annotation (EF Core 6 and newer)

[Unicode(false)] [MaxLength(100)] public string Email { get; set; } = ""; 

Option B: Column type

.Property(c => c.Email).HasColumnType("varchar(100)"); 

Option C: A whole database that uses varchar everywhere

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

Use Option C carefully. It applies to every string property, including new columns your migrations create.

Where This Usually Happens

1. Hand-written models on an existing database. Legacy databases often use varchar, while the C# model was written by hand without saying so.
2. Code that grew over years. One team adds a varchar column in SQL, another maps it in C# later.

If you scaffold your model from the database with Scaffold-DbContext, EF Core usually adds IsUnicode(false) for varchar columns for you. The risk is mostly in models written or edited by hand.

It Is Not Only EF Core

The same trap exists in other data access code.

Dapper sends strings as nvarchar by default too. Use DbString for varchar columns:

var id = await connection.QueryFirstOrDefaultAsync<int>( "SELECT Id FROM dbo.Customers WHERE Email = @Email", new { Email = new DbString { Value = email, IsAnsi = true, Length = 100 } }); 

ADO.NET has the same problem with AddWithValue, which infers NVarChar from a C# string. Set the type yourself:

// Avoid: infers NVarChar command.Parameters.AddWithValue("@Email", email); // Prefer: matches the column exactly command.Parameters.Add("@Email", SqlDbType.VarChar, 100).Value = email; 

Checklist: Find This in Your Own Project

Step 1: List your varchar and char columns.

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('varchar', 'char') ORDER BY TABLE_NAME, COLUMN_NAME; 

Step 2: Check the matching C# properties. Each one should have IsUnicode(false), [Unicode(false)], or a varchar column type.

Step 3: Check your EF Core logs in development. With EnableSensitiveDataLogging(), look at the parameter types for queries on those columns. DbType = AnsiString is good. A plain Size = 4000 on a varchar column is the problem. Never turn sensitive data logging on in production.

Step 4: Search the plan cache for implicit conversions.

SELECT TOP (20) qs.execution_count, qs.total_logical_reads, st.text AS query_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp WHERE CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE N'%CONVERT_IMPLICIT%' ORDER BY qs.total_logical_reads DESC; 

Not every CONVERT_IMPLICIT is a problem. Look for the ones applied to a column in a WHERE or JOIN. This query can be heavy on a busy server, so run it at a quiet time.

Step 5: Check Dapper and ADO.NET code for string parameters sent to varchar columns.

Quick Reference

That last row matters. If a column stores names or text in many languages, the right fix may be to make the column nvarchar, not to make the parameter varchar. Match the types to the data, not the other way around.

Key Takeaways

1. A correct query can still be a slow query.
2. EF Core sends strings as nvarchar unless your model says otherwise.
3. When the types do not match, SQL Server converts the column, and the index cannot be used efficiently.
4. One line of configuration fixes it.
5. The same trap exists in Dapper and ADO.NET.

Over to You

Have you ever found an implicit conversion hiding in a slow query? Was it EF Core, Dapper, or something else? Tell me in the comments.

Thanks for reading! 🙏

If this saved you a slow query, a reaction or a bookmark helps other C# developers find it too.

I write about real-world .NET, C# and SQL Server lessons every week. Follow me so you do not miss the next one.

📧 Hiring a .NET developer who enjoys problems like this? Email me at contact@umerfarooqofficial.com.