Можно ли передать DataTable для специального SQL-запроса в Entity Framework?

Question

Я хотел бы иметь возможность создавать параметризованный специальный SQL-запрос с использованием Entity Framework, который использует параметр с табличным значением.

NB. Вариант использования, который привел меня к этому, заключался в запросе нескольких объектов с указанием списка идентификаторов.Я хочу, чтобы планировщик запросов мог кэшировать план, если это возможно, но я не обязательно хочу создавать хранимую процедуру.

Предположим, у меня есть несколько идентификаторов:

IEnumerable<int> ids = new [] {0, 42, -1};

Если я напишу запрос EF, как

context.MyEntities
    .Where(e => ids.Contains(e.Id))

сгенерированный sqlне параметризован и выглядит так:

SELECT
    [Extent1].[Name] AS [Name]
    FROM [MyEntities] AS [Extent1]
    WHERE [Extent1].[Id] IN (0, 42, -1)

Вместо этого я хочу получить что-то вроде

SELECT
    [Extent1].[Name] AS [Name]
    FROM [MyEntities] AS [Extent1]
    WHERE EXISTS (SELECT
        1
        FROM @ids AS [Extent2]
        WHERE [Extent2].[Id] = [Extent1].[Id]
    )

который полностью параметризован.

Можно ли это сделать в специальном запросе EF?

Я знаю, что можно передавать табличные параметры в прямые запросы, используя EF (например, в хранимую процедуру), используя SqlParameter с SqlDbType.Structured и DataTable в качестве значения (см. /questions/41023047/peredat-tablichnyij-parametr-s-pomoschyu-adonet/41023058#41023058). Когда я пытаюсь сделать то же самое, чтобы создать IQueryable версия моего idsЯ удивлен, обнаружив, что сгенерированный SQL на самом деле перечисляет значения, так что это выглядит как первый (нежелательный) пример SQL, который я привел! Также жалуется The SqlParameter is already contained by another SqlParameterCollection когда я пытаюсь выполнить запрос.

---

Один хакерский способ, который почти работает, состоит в том, чтобы преобразовать IEnumerable Идентификаторы в IQueryable следующим образом:

• Объедините значения в одну строку с разделителями joined
• Сделайте некоторую функцию разделения строк и парсинга с табличным выводом на стороне БД (MyStringSplit)
• Создайте EF "сложный тип" для структуры вывода вышеуказанной функции, например public class IntId { public int Id { get; set; } }
• использование ((IObjectContextAdapter)context).ObjectContext.CreateQuery<IntId>("MyStringSplit(@joined)", new ObjectParameter("joined", joined)) создать IQueryable из моих идентификаторов.

Это производит что-то вроде

SELECT
    [Extent1].[Name] AS [Name]
    FROM [MyEntities] AS [Extent1]
    WHERE [Extent1].[Id] IN (SELECT
        1
        FROM [MyStringSplit](@joined) AS [Extent2]
        WHERE [Extent2].[Id] = [Extent1].[Id]
    )

что близко к тому, что мне нужно, но грязно и, конечно, не дает преимуществ в производительности реальных табличных параметров.

РЕДАКТИРОВАТЬ: Чтобы уточнить, что я имею в виду, это какая-то хорошая абстракция на стороне C#, которую я могу использовать для "преобразования" моего IEnumerable коллекции в IQueryable представления (для конкретного контекста), которые интерпретируются как табличные параметры при использовании EF. Можно предположить, что необходимые типы таблиц уже определены на стороне SQL (например, тип таблицы для целочисленных идентификаторов, тип таблицы для строковых идентификаторов...)

Answer 1

У нас была аналогичная проблема, когда хранилище запросов заполнялось такими типами динамических запросов (на основе предложения переменной "IN"). Поэтому мы изменили его с EF-запроса на встроенный SQL, чтобы сделать его параметризованным запросом и, следовательно, использовать единый план запроса.

IEnumerable<int> ids = new [] {0, 42, -1};
var strIds = string.Join(",", ids);

var names = context.Database.SqlQuery<string>(@"SELECT [NAME]
 FROM [MyEntities] as e
 join STRING_SPLIT(@ids, ',') as i on e.Id = o.value",
  new SqlParameter("@ids", strIds));

ПРИМЕЧАНИЕ. STRING_SPLIT доступен на уровне совместимости 130 или выше на https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15