将LINQ中的字符串连接到SQL。

Is there a way to create a Where lambda on the concatenation of two string fields, such that it will do it properly in SQL? Say I have a person, and there are two fields, FirstName and LastName. I want to be able to filter by their full name, which is shown on the page as FirstName + LastName.

是否有一种方法可以在两个字符串字段的连接上创建Where lambda，以便在SQL中正确执行?假设我有一个人，有两个字段，FirstName和LastName。我希望能够通过它们的全名进行筛选，这在页面上显示为FirstName + LastName。

I can't use a combined property like FullName in the linq lambda, because SQL has no idea what that is, and throws an error. I could have it run the query first by using .AsEnumerable() before the Where but that's less efficient (I think?)

我不能在linq lambda中使用FullName这样的组合属性，因为SQL不知道它是什么，并抛出一个错误。我可以让它先通过在Where之前使用.AsEnumerable()来运行查询，但是这样效率会比较低(我想是吧?)

I tried .Where(p => (p.FirstName + p.LastName).Contains(filterText)), and this runs without error, but it actually can only filter on one at a time. It's like the resulting sql says WHERE FirstName LIKE %text% OR LastName LIKE %text%, rather than searching the concatenation. This means that I can't have my filter text span across the first and last names. If I search for "hn Do" in "John Doe", I get no results.

我尝试。where (p =>)FirstName + p.LastName). contains (filterText))，这不会出错，但它实际上只能一次过滤一个。这就像生成的sql说的一样，FirstName (%text%)或LastName (%text%)，而不是搜索连接。这意味着我不能让我的过滤文本跨越名字和姓氏。如果我在“John Doe”中搜索“hn Do”，我就没有结果。

So is there a correct way to do this in LINQ or do I have to settle for an alternate solution?

那么，在LINQ中是否有正确的方法来实现这一点，或者我必须满足于另一种解决方案?

1 个解决方案

#1

Try:

试一试:

.Where(p => (p.FirstName + " " + p.LastName).Contains(filterText))

Otherwise, you're checking "hn Do" against "JohnDoe", which of course will not match.

否则，您将检查“hnDo”与“JohnDoe”，这当然是不匹配的。

PS

It is not a bug in LINQ to SQL. Your query very clearly asks for an expected behavior that you are not looking for.

它不是LINQ到SQL的bug。您的查询非常明确地要求您不需要的预期行为。

PPS

It's pretty easy to see what SQL your LINQ query generates using LINQPad. In the case of your original query, it produces something like the following:

使用LINQPad很容易看到LINQ查询生成的SQL。在原始查询的情况下，它生成如下内容:

DECLARE @p0 NVarChar(1000) = '%hn Do%'

SELECT [t0].[PersonId], ...
FROM [Person] AS [t0]
WHERE ([t0].[FirstName] + [t0].[LastName]) LIKE @p0

#1