Запрос LINQ с использованием .All() возвращает меньше ожидаемых результатов
Я написал запрос 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. Можно ли это исправить, используя вместо этого синтаксис метода?
Спасибо за прочтение.
Обновлено: Вот SQL, который преобразует LINQ.
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 ''))
То же, что и выше, переформатированное для улучшения читаемости:
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 -- ??? c.col4 ???
OR v.Value LIKE ''
)
)
Обновлено: Вот некоторая информация о данных, возвращаемых двумя порядками терминов:
Для ясности предположим, что все совпадения встречаются в столбце 1. Если условия такие ["term1", "term2"], все результаты будут примерно такими:
"abcterm1 term2"
"123term1 abcterm2"
"term1 term2"
"term123 term2"
"abcterm1 term2"
"term1 term2"
etc...
с другой стороны, если условия поменять местами: ["term2", "term1"], результаты будут выглядеть примерно так:
"term2 term1"
"term2 abcterm1"
"term234 term1"
"123term2 12term1"
etc...
Я добавил переформатированную копию сгенерированного SQL для улучшения читаемости. Общая логика по существу перевела фразу «все значения содержатся где-то в одном из столбцов» на эквивалент «ни одно значение не содержится где-то в одном из столбцов».
Меня привлекают два наблюдения: (1) сгенерированный SQL требует, чтобы все проверяемые столбцы были ненулевыми. Возможно, это отражает семантику C#, где .Contains() выдает ошибку в пустой строке. (2) Коллекция VALUES устанавливает тип значения как char(12) (что интересно, не nchar). Я предполагаю, что ваши фактические значения поиска имеют максимальную длину 12. Когда вы меняете местами два значения поиска, этот сгенерированный SQL по-прежнему использует char(12)? Длина какого-либо из ваших терминов превышает 12 символов? (Я не даю ответов, а просто проверяю факты.) Ручная отладка SQL также может дать подсказки.
Я предлагаю добавить к вашему вопросу тег sql-server для большей видимости (удалив один из других тегов, чтобы удовлетворить ограничение в 5 тегов). Кроме того, образцы данных могут оказаться ключом к получению ответа на эту проблему. Определите и опубликуйте выборочные данные, которые появляются в одном результате, но отсутствуют в другом, а также данные, которые появляются в обоих. Имеет ли значение случай? Получаете ли вы разные результаты при поиске терминов с заглавными, строчными и смешанными буквами? Имеет ли значение длина? Имеют ли поисковые запросы разную длину? Видите ли вы сначала модель поведения для самого короткого и самого долгого срока? Что, если вы запустите запрос синхронно?
@TN Я добавил интерпретируемый SQL незадолго до того, как закончил вчера вечером, не осознавая, что пропустил проверку нуля в своем LINQ. Таким образом, в реальном неупрощенном LINQ проверки на ноль соответствуют SQL. Сейчас я собираюсь добавить проверку нуля в LINQ. Извините, это моя ошибка.
@TN Что касается закономерности в данных, я нашел некоторую интересную информацию, которую сейчас добавлю в сообщение в редактировании.
@TN, что касается вашего второго наблюдения, мои фактические поисковые запросы, которые я тестировал, имеют длину 4 и 5 символов. Тот, который преобразуется в CHAR(12), имеет длину всего 4 символа. Понятия не имею, откуда взялось 12):
Проблема заключается в приведении типа char(12). См. эту db<>скрипку. CAST('term1' AS char(12)) дополняет значения несколькими пробелами, не давая совпадений. Если я изменю его на char(5), он дополнится одним пробелом, что позволяет найти некоторые совпадения в зависимости от наличия и расположения пробелов в ваших данных. На самом деле нам нужен varchar(nnn) или nvarchar(nnn), размер которого соответствует наибольшему значению поиска. Итак, теперь вопрос в том, откуда берется char(12) и как это исправить. К сожалению, у меня нет такого ответа. Возможная ошибка EF Core.
@TN Это звучит правильно и имеет смысл для меня. Спасибо за вашу помощь!
Случайная мысль: мне трудно поверить, что EF Core допустил ошибку и эта ошибка осталась незамеченной и не исправленной. Альтернативное объяснение состоит в том, что кто-то в прошлом нашел и «исправил» проблему в вашем приложении, настроив сгенерированный SQL с помощью CommandInterceptor (или аналогичного), который частично перезаписывает сгенерированный SQL перед его выполнением. Возможно, они уже исправили свою проблему, но теперь это «исправление» нарушает ваш код. Просто дикая догадка.
@TN, это хорошая догадка, но я написал этот проект с нуля, и никто еще к нему не прикасался. Я также хочу упомянуть, что я протестировал выборку поисковых запросов разных размеров, чтобы увидеть, есть ли какие-либо изменения в 12. К сожалению, безуспешно. Я полностью согласен, что маловероятно, что это будет ошибка. У меня нет ощущения, что я делаю здесь что-то новое.
Ответы 1
После огромной помощи от @TN, которую вы можете увидеть в комментариях к исходному посту, у меня возникла идея попробовать добавить .Trim() к term в каждом .Contains():
queryableTerms.All(term =>
row.col1.Contains(term.Trim())
|| row.col2.Contains(term.Trim())
|| row.col3.Contains(term.Trim())
|| row.col4.Contains(term.Trim()))
И это дало мне ожидаемые 355 результатов. Итак, теперь код хотя бы работает.
Однако это не объясняет, откуда взялось приведение к char(12) в переведенном SQL.
Я думаю, что так: ... и термины.Содержит(...). Таблица базы данных является «драйвером»; не «поиск».