EF Core Doesn't Support Some SQL Server Functions!

Not all SQL Server functions are directly supported in EF Core. You can check the full list of SQL Server functions here and EF Core-supported ones here.

One of the missing functions? SOUNDEX – a powerful tool for phonetic search.

What is SOUNDEX? 🔊

SOUNDEX converts a word into a four-character code based on how it sounds in English. Useful for finding similar-sounding words!

Example:

SELECT SOUNDEX('Smith'), SOUNDEX('Smythe');

Output:

S530 S530

Great for searching names that might have multiple spellings! ✨

Creating a Custom SOUNDEX Function in SQL Server 🛠️

We’ll create a function to compare words using SOUNDEX.

CREATE OR ALTER FUNCTION [dbo].[SoundexMatch](@input NVARCHAR(256), @searchTerm NVARCHAR(256))
RETURNS BIT
AS
BEGIN
    DECLARE @match BIT = 0
    DECLARE @inputTable TABLE (word NVARCHAR(256))
    DECLARE @searchTable TABLE (word NVARCHAR(256))

    INSERT INTO @inputTable SELECT value FROM STRING_SPLIT(@input, ' ')
    INSERT INTO @searchTable SELECT value FROM STRING_SPLIT(@searchTerm, ' ')

    IF EXISTS (
        SELECT 1 
        FROM @inputTable a
        JOIN @searchTable s
        ON SOUNDEX(a.word) = SOUNDEX(s.word)
    )
    BEGIN
        SET @match = 1
    END
    RETURN @match
END

Hooking SOUNDEX into EF Core 🔌

Now, let’s expose this SQL function to EF Core.

Updating your DB Context:

public class DatabaseContext(DbContextOptions<DatabaseContext> options) : DbContext(options)
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(DatabaseContext).Assembly);

        var soundexMatchMethodInfo = typeof(InternalDbFunctionsExtensions)
            .GetMethod(nameof(InternalDbFunctionsExtensions.SoundexMatch), [typeof(string), typeof(string)]);

        if (soundexMatchMethodInfo != null)
        {
            modelBuilder
                .HasDbFunction(soundexMatchMethodInfo)
                .HasName("SoundexMatch")
                .HasSchema("dbo");
        }
    }
}

Defining the Function in C#:

public static class InternalDbFunctionsExtensions
{
    public static bool SoundexMatch(string input, string searchTerm) => throw new NotImplementedException();
}

🔍 Using SoundexMatch in Queries

Once you've registered the function in your DbContext, you can use it inside LINQ queries as follows:

public class QuoteService(DatabaseContext context)
{
    public async Task<List<Quote>> SearchQuotesByAuthor(string searchTerm)
    {
        var normalizedSearchTerm = searchTerm.ToUpper();

        return await context.Quotes
            .Where(x => !string.IsNullOrEmpty(x.NormalizedAuthor) &&
                        InternalDbFunctionsExtensions.SoundexMatch(x.NormalizedAuthor, normalizedSearchTerm))
            .ToListAsync();
    }
}

📝 Explanation:

• NormalizedAuthor is assumed to be a normalized (uppercase) version of the author's name.
• InternalDbFunctionsExtensions.SoundexMatch(x.NormalizedAuthor, normalizedSearchTerm) calls the SQL function.
• This query will return quotes where the author's name sounds similar to the searchTerm.

Conclusion

While SOUNDEX is a simple and efficient way to find phonetically similar words, it has limitations:

✅ Pros

• Effective for basic phonetic matching.
• Fast and computationally efficient.
• Standardized encoding for consistent searches.

❌ Cons

• Limited precision – may return false positives.
• English-only focus – struggles with non-English names
• First-letter sensitivity – "Carr" ≠ "Kerr" despite similar sounds.
• Ignores vowels and some consonants, losing important phonetic information.

References 📚

• IBM Public Document – Soundex Algorithm Explained, here.

• Wikipedia – Soundex Algorithm Overview, here.

• ACL Analytics – SOUNDEX Function Guide, here.