Entity Framework Code-First Performance Issue with String Queries

TL;DR – This StackOverflow post explains the observed issue and the resolution

The Problem

I noticed today that a particular lookup in our application was performing quite poorly, so I dug into it with DotTrace and Entity Framework Profiler (we’re using EF Code-First on .NET Framework 4.0 in this particular app). When I zeroed in on the query that was causing the slowdown, I was surprised to find that it was a very simple one that looked something like this:

context.MyTables.Where(m => m.SomeStringProp == stringVar);

Ruling Things Out

Looking at EF Prof, the query it was generating also looked quite simple:

SELECT [Extent1].[ID], [Extent1].[SomeStringProp], [Extent1].[SomeOtherProp],
...
FROM [MyTable] as [Extent1]
WHERE [Extent1].[SomeStringProp] = '1234567890'

The SQL query itself ran almost instantaneously when run through SQL Server Management Studio. Since it wasn’t the query (I thought), I figured it must be the initialization of the EF context, the generation of the SQL query, or the materialization of the MyTable object that was taking so much time.

I ruled out context initialization by adding another EF query before the problem one. This new query, now the first one executed against the context, ran fairly quickly, and the slow one was still slow. Then, I ruled out object materialization by substituting the Where() call with a SqlQuery() call, like so:

context.MyTables.SqlQuery("SELECT [Extent1].[ID] ... WHERE [Extent1].[SomeStringProp] = @param", stringVar);

Doing it this way completely eliminated the performance problem, but SqlQuery() returns fully-tracked entities, just like Where() would, so the bottleneck couldn’t be entity materialization.

The Real Culprit

At this point, I was pretty stumped. Surely the generation of such a simple query from that LINQ statement couldn’t be that slow?

Grasping at straws, I looked at the schema for the table in question, and I noticed something a bit out of the ordinary. The SomeStringProp column was defined as a varchar, not nvarchar like most of the other string columns in the database. Since .NET strings are Unicode, could that have been the problem? I tweaked the SQL query I was running with SSMS to the following to test out the theory:

SELECT [Extent1].[ID], [Extent1].[SomeStringProp], [Extent1].[SomeOtherProp],
...
FROM [MyTable] as [Extent1]
WHERE [Extent1].[SomeStringProp] = N'1234567890'

Bingo! This query ran just as slowly as the LINQ to Entities query.

The Solution

So, how do we tell EF to use the right kind of string when generating the query? It’s as simple as a single data annotation. I changed my entity class like this:

public class MyTable
{
    ...

    [Column(TypeName="varchar")]
    public string SomeStringProp { get; set; }

    ...
}

With that, the performance problem disappeared.

Hopefully this will help someone else who runs into the same issue!

3 thoughts on “Entity Framework Code-First Performance Issue with String Queries

  1. Pingback: Entity Framework Links #5 - ADO.NET Blog - Site Home - MSDN Blogs

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>