Entity Framework stumps SQL performance by defaulting to Unicode

The other day I was working on building a POC which involved a legacy database. I decided to use code first’s fluent API to map out the configuration since the database was pretty complex and I wanted to control how EF sees it.

I always have SQL profiler open when programming EF just to make sure EF is doing the right thing. With EF6 onwards you could also use context.Database.Log to review what EF does underneath.

When I ran my application, I noticed a very visible lag in performance. The table I was working on was very large, but the query was returning only one row, so the impact really stood out. But I knew that the query would not take that long to execute otherwise. I looked at the query in the profiler and at first glance it looked normal. The query was as simple as it could be. I copied it and ran it under query analyser. The query once again took perceivably longer time to finish. This told me it was not EF alone responsible. Looked at the execution plan, it did not look normal. There was an index scan instead of seek and a lot of parallelism thrown in. Then I took a closer look at the query plan and voila the reason for the slow performance became obvious.

Entity Framework defaults to Unicode. This is very easy to overlook and under certain EF strategies and configurations it can really hit your SQL performance. Note, this has nothing to do with EF itself, but how SQL server prepares and executes the query provided by EF.

Let me illustrate with an example.
Below is my sample database. Product with many sales.
Database diagram

Below is how the mapping is defined.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Sale> Sales { get; set; }
}

public class Sale
{
    public int Id { get; set; }
    public int ProdId { get; set; }
    public DateTime SaleDate { get; set; }
    public virtual Product Product { get; set; }
}

public class ProductContext : DbContext
{
    public ProductContext()
        : base("TestDb")
    {
        Database.SetInitializer<ProductContext>(null);
    }

    public DbSet<Product> Products { get; set; }

    public DbSet<Sale> Sales { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>().HasKey(p => p.Id)
            .Property(p => p.Id)
            .HasColumnName("ProdId")
            .HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations
                .Schema.DatabaseGeneratedOption.Identity);

        modelBuilder.Entity<Product>()
            .HasMany(s => s.Sales)
            .WithOptional();

        modelBuilder.Entity<Sale>().HasKey(s => s.Id)
            .Property(s => s.Id)
            .HasColumnName("SaleId")
            .HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations
                .Schema.DatabaseGeneratedOption.Identity);

        modelBuilder.Entity<Sale>()
            .Property(s => s.ProdId)
            .HasColumnName("ProdId");

        modelBuilder.Entity<Sale>()
            .HasRequired(p => p.Product)
            .WithMany(s => s.Sales)
            .HasForeignKey(s => s.ProdId);
    }
}

Below is how EF has interpreted the mapping.
EF model

Let us query for product by name.

1
var prods = prodContext.Products.Where(p => p.Name == "p3").ToList();

EF generates the below query.

1
2
3
SELECT [Extent1].[ProdId] AS [ProdId], [Extent1].[Name] AS [Name]
FROM [dbo].[Products] AS [Extent1]
WHERE N'p3' = [Extent1].[Name]

Pay attention to the Unicode conversion against the search value(N‘p3’). Below is the query plan.
Query plan

Notice how SQL is converting each row to Unicode before the search. This is what is killing your queries performance.
The fix is simple. Just let EF know that the column you are mapping to is not Unicode or is VARCHAR. This will allow EF to issue queries without the conversion.
Add mapping details for the Name property instead of mapping through convention. Specify either Not Unicode or the column type.

1
2
3
4
modelBuilder.Entity<Product>()
    .Property(p => p.Name)
    .IsUnicode(false);
    //.HasColumnType("varchar");

If you approach using model first or code first without an existing database, you would not get into this problem since EF would create the database for you. This is also true if you reverse engineer your database to get your edmx generated. The tool would include the correct datatypes while mapping.

Now this should help me remember and hopefully help others too from stumbling.