LINQ to SQL - custom function

Question

I would like to run a LINQ query like this:

var words = from p in db.Words
   where p.Document.Corpus.Name == corpus
   //where LevenshteinDistance(p.Text.ToCharArray(), word.ToCharArray()) < threshold
   select p;

But if I place the "LevenshteinDistance" function in there it will generate an error:

NotSupportedException: Method 'Char[] ToCharArray()' has no supported translation to SQL.

Is there a correct way to do this?

Answer 1

LINQ to SQL tries to translate the entire expression into SQL. If you want to run your distance function on SQL Server, you'll need to define a SQL Server UDF and map a custom CLR method to that. If you're content to get all the results and then filter client-side on the distance function, use AsEnumerable():

var words = (from p in db.Words
             where p.Document.Corpus.Name == corpus)
             select p)
            .AsEnumerable()
            .Where(p => /* distance function */ < threshold);

The AsEnumerable forces LINQ to SQL to enumerate the query results, allowing the remainder of the query to be resolved using LINQ to Objects and your distance delegate (instead of being translated to SQL).