方法 'Boolean Contains..' 不支持转换为 SQL

问题描述

我的查询中有这个:

var results = (from urls in _context.Urls
               join documents in _context.Documents on urls.UrlId equals documents.DocumentId
               let words = (from words in _context.Words
                            join hits in _context.Hits on words.WordId equals hits.WordId
                            where hits.DocumentId == documents.DocumentId
                            select words.Text).AsEnumerable<string>()

                where urls.ResolvedPath.Contains(breakedQuery, KeywordParts.Url, part) ||
                      documents.Title.Contains(breakedQuery, KeywordParts.Title, part) ||
                      documents.Keywords.Contains(breakedQuery, KeywordParts.Keywords, part) ||
                      documents.Description.Contains(breakedQuery, KeywordParts.Description, part) ||
                      words.Contains(breakedQuery, KeywordParts.Content, part) ...

和包含扩展方法:

用于字符串

public static bool Contains(this string source, IEnumerable<string> values, KeywordParts valuePart, KeywordParts part)
    {
        if (!string.IsNullOrWhiteSpace(source))
            return source.Split(' ').AsEnumerable<string>().Contains(values, valuePart, part);
        return false;
    }

用于枚举(主要方法)

public static bool Contains(this IEnumerable<string> source, IEnumerable<string> values, KeywordParts valuePart, KeywordParts part)
    {
        if (source != null && source.Count() > 0 &&
            values != null && values.Count() > 0 &&
            (part == KeywordParts.Anywhere || valuePart == part))
        {
            foreach (var value in values)
            {
                var has = false;
                var none = (value.StartsWith("-"));
                string term = value.Replace("-", "");

                if (none)
                    has = source.Any(q => !q.Contains(value));
                else
                    has = source.Any(q => q.Contains(values));

                if (has)
                    return has;
            }
        }
        return false;
    }

并且使用 contains 方法抛出异常 NotSupportedException: Method 'Boolean Contains(String, IEnumerable`1[String], KeywordParts, KeywordParts)' 不支持转换为 SQL.

and using Contains method throws exception NotSupportedException: Method 'Boolean Contains(String, IEnumerable`1[String], KeywordParts, KeywordParts)' has no supported translation to SQL.

实际上我想检查每个索引文档是否至少具有指定条件之一

actually i want to check each indexed document if have at lease one of specified conditions

推荐答案

您不能只编写自己的方法并从查询表达式中调用它们 - 查询翻译器不知道该方法的用途.

You can't just write your own methods and call them from your query expression - the query translator has no idea what that method's meant to do.

在获取文档和单词后，您可以强制在 .NET 中执行 where 子句，这可能是......尽管显然这意味着从数据库.可以吗?

You could force the where clause to be executed in .NET after fetching the documents and words, potentially... although obviously that means fetching all the joined data from the database. Would that be okay?

要做到这一点，您需要以下内容:

To do that, you'd want something like:

var tmpQuery = (from urls in _context.Urls
                join documents in _context.Documents 
                on urls.UrlId equals documents.DocumentId
                let words = (from words in _context.Words
                             join hits in _context.Hits 
                             on words.WordId equals hits.WordId
                             where hits.DocumentId == documents.DocumentId
                             select words.Text)
                select new { urls, documents, words };

var query = from r in tmpQuery.AsEnumerable()
            let urls = r.urls.ToList()
            let words = r.words.ToList()
            let documents = r.documents.ToList()
            where urls.ResolvedPath.Contains(breakedQuery, 
                                             KeywordParts.Url, part) ||
               documents.Title.Contains(breakedQuery,
                                        KeywordParts.Title, part) ||
               documents.Keywords.Contains(breakedQuery,
                                           KeywordParts.Keywords, part) || 
               documents.Description.Contains(breakedQuery, 
                                              KeywordParts.Description, part) ||
               words.Contains(breakedQuery, KeywordParts.Content, part)
            select new { urls, words, documents };