EF not using SQL index when filtering with StartsWith

ef-code-first, entity-framework, sql-server

Solution

You need to set your model to also be varchar. You can configure that by overriding `OnModelCreating` in your Context.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Picture>().Property(p => p.Filename).IsUnicode(false);
}

Problem

The following LINQ query filters rows with a `StartsWith()` predicate: ``` db.Pictures.Where(pic => pic.Filename.StartsWith(path)).Count(); ``` Which translates into the following SQL (from SQL Server Profiler): ``` exec sp_executesql N'SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM [dbo].[Pictures] AS [Extent1] WHERE [Extent1].[Filename] LIKE @p__linq__0 ESCAPE N''~'' ) AS [GroupBy1]',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'10429\2\6\%' ``` The `Filename` column is of type VARCHAR(255) and is indexed. However, the query does not use the index because of the N in `ESCAPE N'~'`. In the query execution plan I can see a warning: Type conversion in expression (CONVERT_IMPLICIT(NVARCHAR(255), [Extent1].[Filename], 0)) may affect "CardinalityEstimate" in query plan choice The query runs fine (uses the index) when the N is removed. How can I fix this issue? (One obvious solution might be to change the type of the column to NVARCHAR, but that doesn't seem ideal since I do not actually need to store unicode data)

Original source