LINQ to SQL SOUNDEX - possible?

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?

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

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

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

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

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.

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.