String concatenation in LINQ to SQL

Question

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.

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

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.

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

Answer 1

Try:

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

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

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.

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:

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

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