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