CODEWITHSUNDEEP
c#linq.net-coreentity-framework-core
John Anthony Rinehart
John Anthony Rinehart

Reputation: 35

How to use String.Split() in EF core

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?

Upvotes: 1

Views: 4132

Answers (1)

D Stanley
D Stanley

Reputation: 152521

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.

Upvotes: 6

Related Questions