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?

Community · Accepted Answer · 2020-06-20 09:12:55Z

6

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 8, 2011 at 17:51

StriplingWarrior

158k29 gold badges261 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tesserex Over a year ago

WOW I'm a dumb@$$. For some reason the space crossed my mind for a few seconds and then I dismissed it. Shame is all on me. You'll get the accept as soon as the time lets me.

StriplingWarrior Over a year ago

@Tesserex: Don't be too hard on yourself. Everyone makes mistakes. The key is to realize that 99.9% of the time you think it's a bug with the framework used by tens of thousands of developers worldwide... it's probably just your code. ;-)

Tesserex Over a year ago

Yes that definitely is the case. But I suppose it would have been a bug, if I were correct in the first place.

Collectives™ on Stack Overflow

String concatenation in LINQ to SQL

1 Answer 1

PS

PPS

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

PS

PPS

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related