Не транслируется запрос к SQL Server проверяющий на содержание в базе данных любого значения из списка. C# EF

Question

У меня есть такой код для поиска аналогов по кросс-номерам, он работает, если строка целиком совпадает, но только crossNumbers имеет такой вид "021.181; 021181_SMP; 10502_SOYLU; 1202 1601; ", и мне нужно проверять не строку целиком, а каждую подстроку через точку с запятой.

var query = _context.Products
                .Where(p => p.CrossNumbers.Contains(crossNumbers) && p.Id != selectedProduct);

            var totalItems = await query.CountAsync();

Вот например какие варианты я пробовал еще и с одним итогом - could not be translated:

var crossNumbersArray = crossNumbers.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries)
                                         .Select(cn => cn.Trim())
                                         .ToArray();
    var query = _context.Products
                       .Where(p => crossNumbersArray.Any(cn => p.CrossNumbers.Contains(cn)) && p.Id != selectedProduct);

var crossNumberList = crossNumbers.Split(';', StringSplitOptions.RemoveEmptyEntries)
                                      .Select(cn => cn.Trim())
                                      .ToList();

    var query = _context.Products
        .AsEnumerable() 
        .Where(p => crossNumberList.Any(cn => p.CrossNumbers.Contains(cn)) && p.Id != selectedProduct);

var crossNumberArray = crossNumbers.Split(';', StringSplitOptions.RemoveEmptyEntries);

        var query = _context.Products
            .Where(p => crossNumberArray.Any(cn => EF.Functions.Like(p.CrossNumbers, "%" + cn + "%")) && p.Id != selectedProduct);

var crossNumberArray = crossNumbers.Split(';', StringSplitOptions.RemoveEmptyEntries);

    var query = _context.Products
        .Where(p => crossNumberArray.Any(cn => p.CrossNumbers.Contains(";" + cn + ";")) && p.Id != selectedProduct);

Answer 1

Соединить Enumerable и Queryable в одном запросе нельзя. Нужно или втащить запрос к базе в память (и сделать из него Enumerable), или передавать список в запрос.

Если база большая, а список поиска небольшой, то второе оптимальнее, естественно.

Есть и третий вариант - перебирать список и искать каждый элемент в базе.

List<Product> productsfound = new();
var crossNumbersArray = crossNumbers.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries)
    .Select(cn => cn.Trim()).ToArray();
foreach (string cn in crossNumbersArray)
    productsfound.AddRange(_context.Products
        .Where(p => p.CrossNumbers.Contains(cn) && p.Id != selectedProduct));

Answer 2

В Entity Framework 8 можно использовать коллекции примитивных типов в свойствах.
Предлагаю слегка изменить свойство CrossNumbers, сделав его массивом (или любой другой подходящей коллекцией):

public class Product
{
    public int Id { get; set; }
    public string[]? CrossNumbers { get; set; }
}

В БД это будет храниться в виде массива JSON, в колонке с типом NVARCHAR(MAX).

Перед сохранением в БД строку следует расщепить, превратив в массив. Просто используем Split(';').

db.Products.Add(new Product { CrossNumbers = "A;B;C;D".Split(';') })
db.Products.Add(new Product { CrossNumbers = "E;F;G;H".Split(';') });
db.Products.Add(new Product { CrossNumbers = "B;C;F;G".Split(';') });

Теперь становится легко делать запросы. Просто делаем пересечение двух коллекций с помощью Intersect и проверяем, есть ли хоть одно совпадение: Any.

string crossNumbers = "A;B";
string[] crossNumbersArray = crossNumbers.Split(';');

var result = db.Products
    .Where(p => p.CrossNumbers.Intersect(crossNumbersArray).Any());

Можно и так записать условие:

    .Where(p => p.CrossNumbers.Any(s => crossNumbersArray.Contains(s)));

EF генерирует SQL-запрос с функцией OPENJSON.

БЛОГ НА HUSL

Не транслируется запрос к SQL Server проверяющий на содержание в базе данных любого значения из списка. C# EF

Ответы (2 шт):