LINQ to SQL SOUNDEX - possible?

Question

I have done a little bit of research on this and looked through a few articles both here on StackOverflow as well as some blog posts, but haven't found an exact answer. I also read that it is possible to do it using the 4.0 framework, but have yet to find any supporting evidence.

So my question, is it possible to perform SOUNDEX via a LINQ to SQL Query?

Answer 1

You can do this at the database, by using a fake UDF; in a partial class, add a method to the data context:

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

You can use as an expression like:

x => db.SoundsLike(x.QuoteValue) == db.SoundsLike("text")

Initial idea from: Random row from Linq to Sql

Answer 2

Since .net 4 this will work as well:

from p in mytable
where SqlFunctions.SoundCode(p.MyRow) == SqlFunctions.SoundCode("test")
select p

More info here: http://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions.soundcode.aspx

Answer 3

Add a udf as below

CREATE FUNCTION [dbo].[udfSoundex]
(
    @Soundex nvarchar(100)
)
RETURNS nvarchar(100)
AS
BEGIN
    RETURN Soundex(@Soundex)
END

Simply drag it from server explorer onto you data context in the visual studio dbml file and use it in code as a method exposed on your datacontext class..

Answer 4

That is precisely something which is demonstrated in "LINQ to Objects Using C# 4.0" by Troy Magennis.

EDIT: Adding example tid-bits and clarification: the author's example is for LINQ to objects rather than LINQ to SQL. The author simply made an IEqualityComparer, some pieces of which looked like this...

public class SoundexEqualityComparer : IEqualityComparer<string>
{
  public bool Equals(string x, string y)
  {
     return GetHashCode(x) == GetHashCode(y);
  }

  public int GetHashCode(string obj)
  {
     //e.g. convert soundex code A123,
     //to an integer: 65123
     int result = 0;

     string s = soundex(obj);
     if (string.IsNullOrEmpty(s) == false)
        result = Convert.ToInt32(s[0]) * 1000 +
                 Convert.ToInt32(s.Substring(1, 3));
     return result;
  }

  private string soundex(string s)
  {
     //e.g. book's implementation omitted for this post.
  }
}

//example usage (assuming an array of strings in "names")
var q = names.GroupBy(s => s, new SoundexEqualityComparer() );

Answer 5

For anyone using EF Core 6.0 with SQL Server v16 (2022) ( I haven't tested with any other versions of EF or SQL Server),

[DbFunction(Name = "SOUNDEX", Schema = "SqlServer", IsBuiltIn = true)]
public static string SoundsLike(string query)
{
   throw new NotImplementedException();
}

And its use -

context.TableName.Where(x => SoundsLike(x.Name) == SoundsLike(query));

Answer 6

You can also use the SqlFucntions.Difference method, which maps to the Soundex function:

SqlFunctions.Difference(string, string) returns int - the higher the return value, the more "similar" the strings are.

Answer 7

On the SQL Server, you can wrap SOUNDEX in a UDF (User-Defined function). You can add that to your DataContext class, and then you should be able to use it through the DataContext.