.NET Tips and Tricks

Blog archive

Avoiding Entity Framework Slowdown

Generally speaking, I don't worry much about tweaking my LINQ queries when working with Entity Framework (this is also true when I'm working with SQL directly, by the way). I'm always telling my clients that if they want to speed up their data access they should look at their database design and, especially, how they're using indexes.

There is one exception to that rule, though: If you've got a query doing a comparison to a char or varchar column, then you may be unnecessarily slowing down your LINQ queries. The problem is that Entity Framework assumes that the database equivalent to your string property is a Unicode (nchar or nvarchar) column. If that's not the case -- if your columns are char or varchar -- then you're incurring some data conversion overhead when you use those properties in a Where clause like this:

Dim res = From cust In db.Customers
 	   Where cust.Name = "Vogel"
	   Select cust

That Where clause is going to get converted into a SQL statement that looks like this:

Select *
From Customers
Where Name = N'Vogel'

Because Entity Framework assumes my column is a Unicode column, it slaps the N in front of my string constant ('Vogel') to turn it into a Unicode literal ... and then tries to compare that Unicode literal to my non-Unicode (char or varchar) Name column. SQL Server then has to do the work to convert 'Vogel' back out of Unicode (or it may be converting my Name column to Unicode, either of which is bad).

The solution is to tell Entity Framework that your column is char or varchar by decorating the property with the Column attribute and setting the attribute's TypeName property. This example tells Entity Framework that my Name column is varchar:

<Column(TypeName="varchar")>
Public Property Name As String

For a longer discussion, see Brian Sullivan's blog post on how he got burned. And stop using char and varchar!

Posted by Peter Vogel on 02/23/2018 at 8:59 AM


comments powered by Disqus

Featured

  • Green Motherboard Closeup Graphic

    How To Compute Transformer Architecture Model Accuracy

    Dr. James McCaffrey of Microsoft Research uses the Hugging Face library to simplify the implementation of NLP systems using Transformer Architecture (TA) models.

  • VS Code Snippets Leads Recent Open Source GA Announcements

    GitHub's "Release Radar" for November shows a Visual Studio Code snippets project leading a raft of open source offerings that reached general availability in November.

  • C# Language Server Revamps VS 2022 Razor Editor for ASP.NET Core

    Microsoft is touting new capabilities in ASP.NET Core web development in Visual Studio 2022 thanks to a new Razor editor powered by a Razor Language Server.

  • Windows Forms Lives On in .NET 6

    Windows Forms would like you to know that the reports of its death are greatly exaggerated.

Upcoming Events