Is it possible to pass a DataTable to an ad-hoc sql query in Entity Framework?

Question

I'd like to be able to build a parameterized ad-hoc SQL query using Entity Framework which consumes a table-valued parameter.

NB: The use-case which brought this to my interest was querying for multiple entities given a list of IDs. I want the query planner to be able to cache the plan if possible, but I don't necessarily want to create a stored procedure.

Suppose I have some ids:

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

If I write an EF query like

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

the generated sql is not parameterized, and looks like this:

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

What I want to get instead is something like

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

which is fully-parameterized.

Can this be done in an EF ad-hoc query?

I am aware that it is possible to pass table-valued parameters to direct queries using EF (e.g. to a stored procedure), using a SqlParameter with SqlDbType.Structured and a DataTable as its value (see https://stackoverflow.com/a/10409710/5181199). When I try the same trick to create an IQueryable version of my ids, I'm surprised to find the generated SQL actually enumerates the values so it looks like the first (unwanted) SQL example I gave! It also complains The SqlParameter is already contained by another SqlParameterCollection when I try to execute the query.

---

One hacky way which just about works is to transform the IEnumerable IDs into an IQueryable in the following way:

• Join the values into a single delimited string joined
• Make some kind of string-splitting-and-parsing function with a table-valued output on the DB side (MyStringSplit)
• Create an EF 'complex type' for the output structure of the above function, like public class IntId { public int Id { get; set; } }
• Use ((IObjectContextAdapter)context).ObjectContext.CreateQuery<IntId>("MyStringSplit(@joined)", new ObjectParameter("joined", joined)) to create an IQueryable of my IDs.

This produces something like

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]
    )

which is close to what I'm after, but is messy and surely doesn't provide the performance benefits of actual table-valued parameters.

EDIT: To clarify, what I have in mind is some kind of nice c#-side abstraction which I can use to 'transform' my IEnumerable collections into IQueryable representations (for a particular context) which get interpreted as table-valued parameters when consumed by EF. We can assume that the necessary table types are already defined SQL-side (e.g. a table type for integer IDs, a table type for string IDs...)

Answer 1

We had a similar issue where the query store was getting filled up with these kinds of dynamic queries (based on the variable "IN" clause). So we changed it from an EF query to an inline SQL to make it a parameterized query and therefore use a single query plan.

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));

NOTE: that STRING_SPLIT is available on compatibility level 130 or higher, per https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15