.Net Core 3.1 Linq to Entities - How to concat strings in where clause

user_4200709 0
Viewed 736

I have a user table with [FirstName] and [LastName] columns. I'm trying to build a search function that returns users that meet one of the criteria below:

  • FirstName == myPattern, or
  • LastName == myPattern, or
  • FirstName LastName == myPattern

For example, if I have the following users in my database:

  • Jack One
  • Jack Two
  • Jack Three

I'd like the function to return all of them when the input is Jack, but only return Jack One when the input is Jack One

I currently have the following code:

var users = context.User.Where(x => x.FirstName == pattern 
            || x.LastName == pattern
            || x.FirstName + " " + x.LastName == pattern)

But this does not work as the it gets translated to the following query in MySQL

...WHERE (`p`.`firstName` = 'Jack One') OR (`p`.`lastName` = 'Jack One')) OR (((`p`.`firstName` + ' ') + `p`.`lastName`) = 'Jack One')

It does not work because I believe we need to use CONCAT(firstName, ' ', lastName) if I want to concat multiple strings in MySQL.

I tried using the following .NET functions but they cannot be translated to sql (The LINQ expression ... could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync())

  • string.Join(' ', firstName, lastName)
  • string.Concat(firstName, " ", lastName)

How can I achieve this in .NET CORE 3.1 without pulling all data into memory and evaluating it in client?

Thanks

.net-core linq-to-entities entity-framework mysql
2 Answers

This looks like an case of Linq translating the query in a manner you aren't predicting.

Going from memory and no IDE on hand to check it, but give this a shot. If you split the full name on the space first, you can use the values in your query. 

// returns an array based on the patter, if it can be split
var names = pattern.Split(" ");

// then use the array elements in the query
var users = context.User.Where(x => x.FirstName == pattern 
    || x.LastName == pattern
    || (x.FirstName == names[0] && x.LastName == names[1]));

The last OR condition of the query should then evaulate the 2 new elements in the names array, that was created off the pattern

user_132147680
user_54971890
Related