String.StartsWith不工作波浪号（QUOT;〜＆QUOT;）字符的LINQ to SQL？ [英] String.StartsWith not working with tilde ("~") characters LINQ to SQL?

问题描述

出于某种原因，我到IEnumerable.Where（）的调用使用String.StartsWith（）似乎被赋予不同的结果，取决于它被在LINQ到SQL或标准的LINQ（-to-对象）使用。如果我加入的是什么，否则同一呼叫调用了ToList（），我得到不同的结果返回：

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

这是我的理解是，不像一些在LINQ其他运营商/方法中，其中（）方法的没有的要求predicate是SQL翻译的;它在客户端上执行的，因此可以是任意.NET code。 （我当然抛出其他非SQL code在它成功的结果）。我甚至有一个链接，<一个href="http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/128fe4cf-776d-4200-9f48-7f8dd30dcb34"相对=nofollow>安德斯自己认为，这应该是工作。什么鬼？

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?

编辑：我已经想通了这个问题;它必须做一个波浪在我的搜索字符串的presence。我已经更新了标题，以反映这一点。

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.

推荐答案

目前Konamiman的建议，我的检查日志，看看正在执行的SQL。这里是（的被改写的例子），我得到了什么。

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

第一个调用执行：

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.

第二次调用执行：

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

所以，乔恩斯基特是在正确的轨道上;我发现了问题，因为我碰巧在我的数据/查询条件的波浪。这将导致LINQ到SQL将其标记为转义字符，因此它不会在搜索中使用。 <一href="http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/91c32b17-3a7c-49a8-86c6-55eeb8e36674"相对=nofollow>这个MSDN线程做解释为什么的一份体面的工作;它似乎是在LINQ到SQL code的错误。

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.

建议的解决方法是使用LINQ的SQLMethods.Like（）方法来改变性格逃逸距离〜，​​像这样：

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

这工作，但不幸的是，这是一个LINQ到SQL的只有的解决方案。如果你尝试在LINQ到对象的版本，你得到这个错误：

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.

同样的事情用AsEnumerable（）时，为全碟建议的情况。

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

我试过包装我StartsWith呼叫的方法和使用StartsWith与StringComparisonOption，但这些并没有对LINQ到SQL方面的工作，因为他们不是SQL翻译的。 （因此我对那里（）方法前面的假设是不正确的）。

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).

所以：它看起来像（直到这个bug是固定的），你不能有一个搜索功能，无论工作与波浪线字符的和的是不可知关于LINQ的基本味道。 （如果有一种方法，请务必将它张贴）。

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).

这篇关于String.StartsWith不工作波浪号（QUOT;〜＆QUOT;）字符的LINQ to SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！