[SOLVED] Entity Framework query slow, but same SQL in SqlQuery is fast

Entity Framework query slow, but same SQL in SqlQuery is fast

I'm seeing some really strange perf related to a very simple query using Entity Framework Code-First with .NET framework version 4. The LINQ2Entities query looks like this:

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

This takes over 3000 milliseconds to execute. The generated SQL looks very simple:

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

This query runs almost instantaneously when run through Management Studio. When I change the C# code to use the SqlQuery function, it runs in 5-10 milliseconds:

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

So, exact same SQL, the resulting entities are change-tracked in both cases, but wild perf difference between the two. What gives?

Solution

Found it. It turns out it's an issue of SQL data types. The SomeStringProp column in the database was a varchar, but EF assumes that .NET string types are nvarchars. The resulting translation process during the query for the DB to do the comparison is what takes a long time. I think EF Prof was leading me astray a bit here, a more accurate representation of the query being run would be the following:

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

So the resulting fix is to annotate the code-first model, indicating the correct SQL data type:

public class MyTable
{
    ...

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

    ...
}