How to use String.Split() in EF core-CodePudding

I have a list of names in the DB that I want to filter from a field. But it has to match the beginning of the word, not the middle.

let's say I have this in the DB:

"foobar"
"bar foo"
"bar fos foo"
"barfoo"
"bar basfoo"

if I type "foo", I should get:

"foobar"
"bar foo"
"bar fos foo"

I tried this already:

query.Where(x => x.Split(" ", StringSplitOptions.None).Any(y => y.StartsWith(name)))

but apparently .Split() cannot be translated to SQL. I also cannot use a combination of .IndexOf() and .Substring(), since the names can have multiple places that should be split.

And I don't want to switch to client side evaluation for this.

Is there any way to translate Split() to SQL?

CodePudding user response：

No, there is not a way to translate Split to SQL becasue SQL does not have a standard function for splitting strings*. There are some methods to do it, but IMHO they are messy and completely unintuitive.

To solve your problem, I would pull back maybe more data than I need and do the splitting/filtering in-memory. Something like:

query.Where(x => x.Contains(name))
     .AsEnumerable()
     .Where(x => x.Split(" ", StringSplitOptions.None).Any(y => y.StartsWith(name)))

That will pull any record that contains the name anywhere in the string, then do the splitting in memory to match the start of one of the sections.

You could be a bit more surgical by doing:

query.Where(x => x.StartsWith(name) || x.Contains(" "   name))
     .AsEnumerable()
     .Where(x => x.Split(" ", StringSplitOptions.None).Any(y => y.StartsWith(name)))

But the difference in performance will depend on the size of your dataset and how many false-positives you get in the first method.

*SQL Server 2016 seems to have added a string_split method, but there may not be support for it in EF Core yet.

CodePudding user response：

You must use the REGEXP operator. MySQL REGEXP will perform a case insensitive regex check by default, but you still can use [A-Za-z] just in case you have non-standard options. Use:

WHERE name REGEXP '^[a-zA-Z[:space:]]*searchstring[a-zA-Z[:space:]]*$'

If you do not care about what there is in the entries and you just need to find those containing your string, use LIKE with % (=any text):

WHERE name LIKE '%searchstring%'

CodePudding user response：

EF Core (from version 6.2) has a method equivalent to the SQL Like method in the static DbFunctions class.

You should be able to do something like this:

query.Where(x => DbFunctions.Like(x, $"%{name}%"));