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:
For example, if I have the following users in my database:
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())
How can I achieve this in .NET CORE 3.1 without pulling all data into memory and evaluating it in client?
Thanks
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
It seems a bug of MySql.Data.EntityFrameworkCore.
I use Pomelo.EntityFrameworkCore.MySql instead to solve the problem.