Use Soundex search with EF Core

I am trying to implement Soundex searching in EF Core, I have written this LINQ that should query a table called 'Customers' by first and last name.

using System.Data.Entity.SqlServer;
//More code
customers = hisDb.Customers.Where(p => SqlFunctions.SoundCode(p.Fname.Trim()) == SqlFunctions.SoundCode(FName.Trim()) && SqlFunctions.SoundCode(p.Lname.Trim()) == SqlFunctions.SoundCode(LName.Trim())).AsEnumerable().ToList();

However, I am getting this error.

> System.NotSupportedException: This function can only be invoked from LINQ to Entities.
at System.Data.Entity.SqlServer.SqlFunctions.SoundCode(String arg)

Anyway getting Soundex search to work with EF Core 6?

What you should do is pre-calculate the Soundex-values for all of your Customers' first names, and store (and index) those values in your database. Then query against those values using the Soundex-value of you search argument.

For instance

ALTER TABLE Customers
ADD FirstNameSoundex AS SOUNDEX(FirstName);

CREATE INDEX Customers_FirstName_Soundex ON Customers (FirstNameSoundex);

Update the DbContext to add this new computed column and query

var soundCode = SqlFunctions.SoundCode(arg);
hisDb.Customers.Where(cust => cust.FirstNameSoundex == soundCode)

This way you won't have to scan and SOUNDEX() the entire table at every query, and the database doesn't have to know about your particular SqlFunction.

(disclaimer: I don't know if SqlFunctions.SoundCode is in fact the same as T-SQL SOUNDEX() but if so, the above should be fine)

Since SO allows a user to answer his own question. I have found this solution, but I do not know how EF Core friendly is.

In DbContext, I added

[DbFunction(Name = "SoundEx", IsBuiltIn = true]
    public static string SoundEx(string input)
    {
        throw new NotImplementedException();
    }

and called it in my code

customers = hisDb.Customers.Where(p => DbContext.SoundEx(p.Fname.Trim()) == DbContext.SoundEx(FName.Trim()) && DbContext.SoundEx(p.Lname.Trim()) == DbContext.SoundEx(LName.Trim())).ToList();