Watch out for StartsWith when upgrading from .net 3.5 to 4.0

Currently I am working on .net 3.5 project so i am having to live with Entity Framework. v1. Anyways i was working on a linq query that was making use of StartsWith operator. For some reason based on which page you are coming from the value passed in to the StartWith was sometimes empty. To be more clear let’s look at a sample code to understand how my scenario looked like.

When you run the above code, you get 0 records back. If you look at the sql generated for the above query, it uses CharIndex to check if the value passed in is found in position index 1 of the ContactName. Following figure shows the query generated.

When you upgrade this query to .net 4.0 and run the same query in EF4, you would actually get 91 records . Let’s look at query generated in EF4.

Notice the query in EF4 uses a  Like syntax compared to CharIndex. This was actually an improvement in the query generation requested by the community. The reason is, Like operator would use an index on the column where as CharIndex will not. However the side effect of the like operator when comparing it with empty value causes all records to be returned where contactname is not null. In simple terms you can rewrite the above sql query to the one below.

select * from customers where contactname like ‘%’

The above sql says show me all customers that have some values that includes contactnames which are empty but excludes ones where conactname is null.

In our case we had 100000 records being returned causing pages to timeout. I am glad i knew about this change for EF4 and i was able to resolve the problem easily.

Oh wait, i didn’t tell u the solution. Here is a simple solution.

var custs = db.Customers.Where(c => !string.IsNullOrEmpty(startswith) && c.ContactName.StartsWith(startswith)).ToList();