DEV Community

Cover image for EF Core Doesn't Support Some SQL Server Functions!
Girgis Adel
Girgis Adel

Posted on

EF Core Doesn't Support Some SQL Server Functions!

#webdev #aspdotnet #sqlserver #soundex

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');
Enter fullscreen mode Exit fullscreen mode

Output: 

S530 S530
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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"); } } }
Enter fullscreen mode Exit fullscreen mode

Defining the Function in C#: 

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

🔍 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(); } }
Enter fullscreen mode Exit fullscreen mode

📝 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.

Top comments (0)