SQL produced by Entity Framework for string matching

Question

Given this linq query against an EF data context:

var customers = data.Customers.Where(c => c.EmailDomain.StartsWith(term))

You’d expect it to produce SQL like this, right?

SELECT {cols} FROM Customers WHERE EmailDomain LIKE @term+’%’

Well, actually, it does something like this:

SELECT {cols} FROM Customer WHERE ((CAST(CHARINDEX(@term, EmailDomain) AS int)) = 1)

Do you know why?

Also, replacing the Where selector to:

c => c.EmailDomain.Substring(0, term.Length) == term

it runs 10 times faster but still produces some pretty yucky SQL.

NOTE: Linq to SQL correctly translates StartsWith into Like {term}%, and nHibernate has a dedicated LikeExpression.

Answer 1

I don't know about MS SQL server but on SQL server compact LIKE 'foo%' is thousands time faster than CHARINDEX, if you have INDEX on seach column. And now I'm sitting and pulling my hair out how to force it use LIKE.

http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/1b835b94-7259-4284-a2a6-3d5ebda76e4b

Answer 2

The reason is that CharIndex is a lot faster and cleaner for SQL to perform than LIKE. The reason is, that you can have some crazy "LIKE" clauses. Example:

SELECT * FROM Customer WHERE EmailDomain LIKE 'abc%de%sss%'

But, the "CHARINDEX" function (which is basically "IndexOf") ONLY handles finding the first instance of a set of characters... no wildcards are allowed.

So, there's your answer :)

EDIT: I just wanted to add that I encourage people to use CHARINDEX in their SQL queries for things that they didn't need "LIKE" for. It is important to note though that in SQL Server 2000... a "Text" field can use the LIKE method, but not CHARINDEX.