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

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.