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?

Roaring

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

Gasiform

For those whho tried with newer versions: (and for my own future reference) [DbFunction("SqlServer","SOUNDEX")] – Haile

Sorry but how is this an answer you didnt provider a way for the user to do it just the interface – Occasionally

Answer 2

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

Vaporetto

Answer 3

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

Covering

This appears to be a reference to Entity Framework and NOT Linq to SQL which explains why there are no up-votes. – Bootlace

@Bootlace True, my bad. But I found this question searching for an EF solution, so maybe it will help someone. – Covering

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() );

Vaporizer

And for those who don't have the book, do you have an example? – Venu

Though I thank you for the response Mystagogue, an example or a link to an example would be more beneficial than a link to a book I can purchase. – Roaring

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));

Ouch

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.

Lacilacie

This is EF, not Linq To SQL. – Duro

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.

Veiled

Would you be able to provide an example of this please? – Roaring