使用.All()的LINQ查询返回结果不符预期的问题排查

我写了一段LINQ查询，用于在SQL数据库中搜索包含多个指定字符串的数据，为方便演示，将搜索字符串硬编码为["term1", "term2"]：

var terms = ["term1", "term2"];
var queryableTerms = terms.AsQueryable();

var results = await(
  from row in _context.table
    where
      row.col1 != null && row.col2 != null && row.col3 != null && row.col4 != null
      && queryableTerms.All(term =>
        row.col1.Contains(term)
        || row.col2.Contains(term)
        || row.col3.Contains(term)
        || row.col4.Contains(term))
    select row
  )
.ToListAsync();

这段代码返回约60条结果，但如果把terms改成反向的["term2", "term1"]，返回结果就变成了约250条。另外，如果改成显式检查每个搜索词的方式：

...
   && (row.col1.Contains(terms[0])
      || row.col2.Contains(terms[0])
      || row.col3.Contains(terms[0])
      || row.col4.Contains(terms[0]))
   && (row.col1.Contains(terms[1])
      || row.col2.Contains(terms[1])
      || row.col3.Contains(terms[1])
      || row.col4.Contains(terms[1]))
...

此时数据库返回约350条结果。我已经确认代码本身没有问题，问题应该出在LINQ的转换逻辑上，想问改用方法语法能不能解决这个问题？

LINQ转换后的SQL #

原始转换SQL：

SELECT *
FROM [dbo].[table] AS [c]
WHERE [c].[col1] IS NOT NULL AND [c].[col2] IS NOT NULL AND [c].[col3] IS NOT NULL AND [c].[col4] IS NOT NULL AND NOT EXISTS (
    SELECT 1
    FROM (VALUES (CAST('term1' AS char(12))), ('term2')) AS [v]([Value])
    WHERE NOT (CHARINDEX([v].[Value], UPPER([c].[col1])) > 0 OR [v].[Value] LIKE '' OR CHARINDEX([v].[Value], UPPER([c].[col2])) > 0 OR [v].[Value] LIKE '' OR CHARINDEX([v].[Value], UPPER([c].[col3])) > 0 OR [v].[Value] LIKE '' OR CHARINDEX([v].[Value], UPPER([c].[col4])) > 0 OR [v].[Value] LIKE ''))

格式化后的SQL：

SELECT *
FROM dbo.table AS c
WHERE c.col1 IS NOT NULL
AND c.col2 IS NOT NULL
AND c.col3 IS NOT NULL
AND c.col4 IS NOT NULL
AND NOT EXISTS (
    SELECT 1
    FROM (
        VALUES
            (CAST('term1' AS char(12))),
            ('term2')
    ) AS v(Value)
    WHERE NOT (
        CHARINDEX(v.Value, UPPER(c.col1)) > 0
        OR v.Value LIKE ''
        OR CHARINDEX(v.Value, UPPER(c.col2)) > 0
        OR v.Value LIKE ''
        OR CHARINDEX(v.Value, UPPER(c.col3)) > 0
        OR v.Value LIKE ''
        OR CHARINDEX(v.Value, UPPER(c.col4)) > 0
        OR v.Value LIKE ''
    )
)

不同terms顺序的结果示例（假设匹配均在col1） #

当terms为["term1", "term2"]时，返回结果示例：

"abcterm1 term2"
"123term1 abcterm2"
"term1 term2"
"term123 term2"
"abcterm1 term2"
"term1 term2"
etc...

当terms反向为["term2", "term1"]时，返回结果示例：

"term2 term1"
"term2 abcterm1"
"term234 term1"
"123term2 12term1"
etc...

内容的提问来源于stack exchange，提问作者Jye