.net - Linq 到实体 : does not recognize the method

Question

我尝试使用 Levenshtein Distance Linq 选择查询(如下所示)，它抛出异常。

IEnumerable<Host> closeNeighbours = (from h in _dbContext.People
                                         let lD = Utilities.LevenshteinDistance(lastName, h.LastName)
                                         let length = Math.Max(h.LastName.Length, LastName.Length)
                                         let score = 1.0 - (double)lD / length
                                         where score > fuzziness

                                         select h);



public static int LevenshteinDistance(string src, string dest)
{
    int[,] d = new int[src.Length + 1, dest.Length + 1];
    int i, j, cost;
    char[] str1 = src.ToCharArray();
    char[] str2 = dest.ToCharArray();

    for (i = 0; i <= str1.Length; i++)
    {
        d[i, 0] = i;
    }
    for (j = 0; j <= str2.Length; j++)
    {
        d[0, j] = j;
    }
    for (i = 1; i <= str1.Length; i++)
    {
        for (j = 1; j <= str2.Length; j++)
        {

            if (str1[i - 1] == str2[j - 1])
                cost = 0;
            else
                cost = 1;

            d[i, j] =
                Math.Min(
                    d[i - 1, j] + 1,              // Deletion
                    Math.Min(
                        d[i, j - 1] + 1,          // Insertion
                        d[i - 1, j - 1] + cost)); // Substitution

            if ((i > 1) && (j > 1) && (str1[i - 1] ==
                str2[j - 2]) && (str1[i - 2] == str2[j - 1]))
            {
                d[i, j] = Math.Min(d[i, j], d[i - 2, j - 2] + cost);
            }
        }
    }

    return d[str1.Length, str2.Length];
}

这似乎不起作用。还有其他选择吗？

异常:用户代码未处理 System.NotSupportedException Message=LINQ to Entities 无法识别“Int32 LevenshteinDistance(System.String, System.String)”方法，并且此方法无法转换为存储表达式。 来源=System.Data.Entity

Answer 1

您不能在 Entity Framework 查询中使用该函数，因为 EF 将无法将其转换为适当的 TSQL。您必须将源序列放入内存中，允许数据库中适用的任何过滤器，然后在 linq-to-objects 中执行其余部分。这只是一个细微的变化。

var closeNeighbors = from h in db.People.AsEnumerable() // bring into memory
                     // query continued below as linq-to-objects
                     let lD = Utilities.LevenshteinDistance(lastName, h.LastName) 
                     let length = Math.Max(h.LastName.Length, LastName.Length) 
                     let score = 1.0 - (double)lD / length 
                     where score > fuzziness 
                     select h; 

AsEnumerable() 之前的所有操作都将在数据库中发生。如果存在普遍适用于 People 的过滤器，您可以在 AsEnumerable() 调用之前使用这些过滤器。示例

var mixedQuery = db.People
                     .Where(dbPredicate).OrderBy(dbOrderSelector) // at the database
                     .AsEnumerable() // pulled into memory
                     .Where(memoryPredicate).OrderBy(memoryOrderSelector);