LINQ to SQL-自定义功能 [英] LINQ to SQL - custom function

问题描述

我想运行这样的LINQ查询:

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;

但是，如果我在其中放置"LevenshteinDistance"功能，则会产生错误:

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

NotSupportedException:方法'Char [] ToCharArray()'不支持SQL转换.

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

有正确的方法吗?

推荐答案

LINQ to SQL尝试将整个表达式转换为SQL.如果要在SQL Server上运行距离函数，则需要定义一个SQL Server UDF并将一个自定义CLR方法映射到该函数.如果您愿意获取所有结果，然后对距离函数进行客户端过滤，请使用AsEnumerable():

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

AsEnumerable强制LINQ to SQL枚举查询结果，从而允许使用LINQ to Objects和您的距离委托(而不是转换为SQL)来解决查询的其余部分.

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

这篇关于LINQ to SQL-自定义功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！