String.StartsWith not working with tilde ("~") characters LINQ to SQL?

Question

For some reason, my call to IEnumerable.Where() using String.StartsWith() appears to be giving different results depending on whether it's being used in LINQ-to-SQL or standard LINQ (-to-objects). If I add in a call to ToList() on what's otherwise the same call, I get different results back:

var withToList = MyDataContext.MyEntities.ToList().Where(entity => entity.Name.StartsWith("~Test: My Test String"));
// withToList.Count() returns 5, which is what I expect.
var direct = MyDataContext.MyEntities.Where(entity => entity.Name.StartsWith("~Test: My Test String"));
// direct.Count() returns 0

It's my understanding that, unlike some of the other operators/methods in LINQ, the Where() method does not require the predicate to be SQL-translatable; it's executed on the client side and thus can be arbitrary .NET code. (I've certainly thrown other non-SQL code at it with successful results). I've even got a link where Anders himself suggests that this should be working. What the heck?

EDIT: I've figured out the problem; it has to do with the presence of a tilde in my search string. I've updated the title to reflect this.

Answer 1

In the first case, indeed the Where predicate doesn't need to be translatable to SQL, since you are first getting the whole table to memory (ToList) and then doing the filtering.

In the second case, the Where predicate needs to be translatable to a SQL WHERE clause, since the filtering is done in the database. TheString.StartsWith method is translated to a SQL LIKE statement.

Remember that you can take a look at the generated SQL by using the DataContext.Log property. This should help you to understand how it works.

Answer 2

This query will be executing in SQL - so you may see some oddities depending on how SQL is handling the "LIKE". I suggest you find out what query it's running, and try running it yourself in SQL Management Studio. In this case it seems unusual that it's not working though - it could be a bug in LINQ to SQL; it may not be escaping things properly. (Does ~ mean anything special in a SQL LIKE clause?)

Anything which is meant to be part of what's executed at the database side does need to be SQL-translatable. It can't contain arbitrary .NET code - only code which LINQ-to-SQL is able to translate. Otherwise composition gets broken - if you add a join, or an ordering etc afterwards, it would become very hard indeed to do some of the processing in SQL and some on the client side, mixing the two. You can do some in SQL and then some on the client side though. Note that you can use AsEnumerable as an alternative to ToList to get the rest of the query to be executed in-process.

Answer 3

At Konamiman's suggestion I checked the log to see what SQL was being executed. Here's (a munged example of) what I got.

The first call executes:

SELECT [t0].[ID], [t0].[Name]
FROM [dbo].[MyEntity] AS [t0]

That makes sense, as the filtering is happening on the client-side, so we need to query for all rows.

The second call executes:

SELECT COUNT(*) AS [value]
FROM [dbo].[MyEntity] AS [t0]
WHERE [t0].[Name] LIKE @p0 ESCAPE '~'

So, Jon Skeet was on the right track; I'm getting the problem because I happen to have a tilde in my data/query condition. This causes LINQ-to-SQL to mark it as an escape character, and thus it doesn't get used in the search. This MSDN thread does a decent job of explaining why; it appears to be a bug in the LINQ-to-SQL code.

The suggested workaround is to use LINQ's SQLMethods.Like() method to change the escaping character away from "~", like so:

var direct = MyDataContext.MyEntities.Where(entity => SQLMethods.Like(entity.Name, "~Test: My Test String%", "!");
// direct.Count() now returns 5 as expected

This works, but unfortunately this is a LINQ-to-SQL only solution. If you try it on the LINQ-to-Object version, you get this error:

System.NotSupportedException : Method 'Boolean Like(System.String, System.String, Char)' 
cannot be used on the client; it is only for translation to SQL.

The same thing happens when using AsEnumerable() as Jon Skeet suggested.

I tried wrapping my StartsWith call in a method and using StartsWith with a StringComparisonOption, but these don't work on the LINQ-to-SQL side because they're not SQL-translatable. (And thus my earlier assumption about the Where() method was incorrect).

So: it looks like (until this bug is fixed) you cannot have a searching function that both works with tilde characters and is agnostic about the underlying flavour of LINQ. (If there is a method, please be sure to post it).