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?

itowlson · Accepted Answer · 2009-11-09 03:24:35Z

6

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

answered Nov 9, 2009 at 3:24

itowlson

75k18 gold badges165 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

LINQ to SQL - custom function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related