SQL generated by Entity Framework for string matching

Question

Given this linq query in the context of EF data:

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

You expect it to generate SQL like this, right?

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

Well, actually, he 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 with:

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

it works 10 times faster, but it creates a pretty pretty yqy.

NOTE. Linq to SQL correctly translates StartsWith to Like {term}%, and nHibernate has a special expression for the expression.

Answer 1

The reason is that CharIndex is much faster and cleaner for SQL than LIKE. The reason is that you might have some crazy LIKE sentences. Example:

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

But the "CHARINDEX" function (basically "IndexOf") ONLY handles the search for the first instance of the character set ... no wildcards are allowed.

So here is your answer :)

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

Answer 2

I don't know about MS SQL Server, but on the SQL LIKE CD, "foo%" is thousands of times faster than CHARINDEX if you have an INDEX index per column. And now I sit and stretch my hair, how to make him use LIKE.

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

Answer 3

Performance seems to be roughly equal to LIKE and CHARINDEX, so this should not be the reason. See here or here for a discussion. Also, CAST is very strange because CHARINDEX returns an int.

Answer 4

charindex returns the location of the first member in the second member.

sql starts with 1 as the first location (0 = not found)

http://msdn.microsoft.com/en-us/library/ms186323.aspx

I don’t know why it uses this syntax, but that it works

Answer 5

I agree that this is not faster, I extracted tens of thousands of rows from our database with the letter i name. However, I found that you need to use>, not = ... so use

 {cols} FROM Customer WHERE ((CAST(CHARINDEX(@term, EmailDomain) AS int)) > 0) 

but not

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

Here are my two tests ....

 select * from members where surname like '%i%' --12 seconds select * from sc4_persons where ((CAST(CHARINDEX('i', surname) AS int)) > 0) --12 seconds select * from sc4_persons where ((CAST(CHARINDEX('i', surname) AS int)) = 1) --too few results