Categories
c# entity-framework parameterized-query sql sql-server

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

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