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

Question

For some reason, my call to IEnumerable.Where () using String.StartsWith () seems to give different results depending on whether it is used in LINQ-to-SQL or standard LINQ (-to-objects). If I add to the ToList () call, which otherwise is 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 

I understand that, unlike some other LINQ statements / methods, the Where () method does not require the predicate to be SQL translatable; it runs on the client side and therefore can be arbitrary .NET code. (Of course, I chose another non-SQL code for it with successful results). I even have a link where Anders himself suggests that this should work. What the hell?

EDIT: I figured out the problem; this is due to the presence of a tilde in my search bar. I updated the title to reflect this.

Answer 1

In a Konamiman sentence, I checked the log to see if SQL was running. Here (a sketched example) that I received.

The first call is made:

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

This makes sense since filtering is done on the client side, so we need to query all rows.

The second call is made:

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

So John Skeet was on the right track; I have a problem because I have a tilde in the data / request type. This causes LINQ-to-SQL to put it as an escape character, and therefore it is not used in the search. This MSDN thread does a decent job explaining why ; this is apparently a bug in LINQ to SQL code.

The suggested workaround is to use the LINQ SQLMethods.Like () method to change the escape character from "~", for example:

 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 it is a LINQ-to-SQL solution. If you try it in the LINQ-to-Object version, you will 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 suggested by Jon Skeet.

I tried wrapping my StartsWith call in a method and using StartsWith with StringComparisonOption, but they do not work on the LINQ-to-SQL side because they are not SQL-translatable. (And thus, my previous assumption about the Where () method was wrong).

So: it seems (until this error has been fixed) you will not have a search function that works with tilde characters and is agnostically related to the basic LINQ flavor. (If there is a way, be sure to post it).

Answer 2

This query will be executed in SQL - so you may see some oddities depending on how SQL handles "LIKE". I suggest you find out what query it is doing and try to run it yourself in SQL Management Studio. In this case, it seems unusual that it does not work, although this may be a mistake in LINQ to SQL; perhaps it does not slip away. (Does anything special in the SQL LIKE clause?)

Everything that should be part of what is done on the database side must be SQL translatable. It cannot contain arbitrary .NET code that LINQ-to-SQL can translate. Otherwise, the composition will break - if after that you add a connection or order, etc., it is very difficult to perform some processing in SQL, and some on the client side, mixing them. You can do some of the SQL, and then some on the client side. Note that you can use AsEnumerable as an alternative to ToList to execute the remainder of the request in the process.

Answer 3

In the first case, indeed, the Where predicate should not be translatable in SQL, since you first get the entire table into memory ( ToList ), and then do the filtering.

In the second case, the Where predicate must be translatable into the SQL WHERE clause, since filtering is performed in the database. TheString.StartsWith method translates into an SQL LIKE statement.

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