I have written a LINQ query where I am searching an SQL database with a number of strings. For demonstration purposes, I’ll hard-code the search strings as ["term1", "term2"]:

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

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

This code returns around 60 results. However, if I define terms in reverse as ["term2", "term1"], I get around 250 results. What could cause this to happen?

Additionally, if I explicitly check all the columns like this:

...
   && (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]))
...

I get around 350 results from the database.

What am I doing wrong? I have had other people check my actual code and there is nothing wrong. The issue seems to be with the behaviour of LINQ. Is this something that can be fixed by using method syntax instead?

Thanks for reading.

EDIT: Here is the SQL that LINQ translates to

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].[4])) > 0 OR [v].[Value] LIKE ''))