CODEWITHSUNDEEP
sqllinq-to-sqlsoundex
Steve Hayes
Steve Hayes

Reputation: 505

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?

Upvotes: 14

Views: 8705

Answers (7)

Bandook
Bandook

Reputation: 708

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

Upvotes: 2

DevDave
DevDave

Reputation: 1049

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

Upvotes: 24

Mark Shapiro
Mark Shapiro

Reputation: 1232

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.

Upvotes: 1

Marthijn
Marthijn

Reputation: 3392

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

Upvotes: 6

Coding Flow
Coding Flow

Reputation: 21881

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

Upvotes: 6

Brent Arias
Brent Arias

Reputation: 30165

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

Upvotes: 2

driis
driis

Reputation: 164281

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.

Upvotes: 0

Related Questions