J'aimerais pouvoir générer une requête SQL ad-hoc paramétrée en utilisant Entity Framework qui consum un paramètre table.
NB: Le cas d'utilisation qui a attiré mon attention était l'interrogation de plusieurs entités avec une list d'identifiants. Je souhaite que le planificateur de requêtes puisse mettre en cache le plan si possible, mais je ne souhaite pas nécessairement créer une procédure stockée.
Supposons que j'ai des identifiants:
IEnumerable<int> ids = new [] {0, 42, -1};
Si j'écris une requête EF comme
context.MyEntities .Where(e => ids.Contains(e.Id))
le sql généré n'est pas paramétré et ressemble à ceci:
SELECT [Extent1].[Name] AS [Name] FROM [MyEntities] AS [Extent1] WHERE [Extent1].[Id] IN (0, 42, -1)
Ce que je veux get à la place est quelque chose comme
SELECT [Extent1].[Name] AS [Name] FROM [MyEntities] AS [Extent1] WHERE EXISTS (SELECT 1 FROM @ids AS [Extent2] WHERE [Extent2].[Id] = [Extent1].[Id] )
qui est entièrement paramétré.
Cela peut-il être fait dans une requête ad hoc EF?
Je suis conscient qu'il est possible de passer des parameters table à des requêtes directes en utilisant EF (par exemple à une procédure stockée), en utilisant un SqlParameter
avec SqlDbType.Structured
et un DataTable
comme valeur (voir https://stackoverflow.com/a / 10409710/5181199 ). Lorsque j'essaie la même astuce pour créer une version IQueryable
de mes ids
, je suis surpris de constater que le SQL généré énumère réellement les valeurs, de sorte qu'il ressemble au premier exemple SQL (indésirable) que j'ai donné! Il se plaint également The SqlParameter is already contained by another SqlParameterCollection
quand j'essaye d'exécuter la requête.
Une façon hacky qui fonctionne à peu près est de transformer les ID IEnumerable
dans un IQueryable
de la façon suivante:
joined
MySsortingngSplit
) public class IntId { public int Id { get; set; } }
public class IntId { public int Id { get; set; } }
((IObjectContextAdapter)context).ObjectContext.CreateQuery<IntId>("MySsortingngSplit(@joined)", new ObjectParameter("joined", joined))
pour créer un IQueryable
de mes ID. Cela produit quelque chose comme
SELECT [Extent1].[Name] AS [Name] FROM [MyEntities] AS [Extent1] WHERE [Extent1].[Id] IN (SELECT 1 FROM [MySsortingngSplit](@joined) AS [Extent2] WHERE [Extent2].[Id] = [Extent1].[Id] )
ce qui est proche de ce que je suis après, mais est en désordre et ne fournit certainement pas les avantages de performance des parameters de table réels.
EDIT : Pour clarifier, ce que j'ai en tête est une sorte d'abstraction sympa que je peux utiliser pour transformer mes collections IEnumerable
en représentations IQueryable
(pour un context particulier) qui sont interprétées comme des parameters de table lorsqu'elles sont consommées par EF. Nous pouvons supposer que les types de tables nécessaires sont déjà définis côté SQL (par exemple un type de table pour les ID entiers, un type de table pour les ID de string …)