Pourquoi ESQL c'est bien ?
ESQL permet d'écrire une requête sous la forme d'une chaine de caractère qui va se baser sur le modèle. Chaque provider de l'Entity Framework est capable de convertir de l'esql en son sql.
Cependant, à l'heure de LINQ, quel intérêt ?
Comme me l'a montré Frédéric, il y a un projet DynamicQuery dans les samples fournit avec VS 2008 qui permet d'interpréter des clauses sous forme de chaine de caractères qui peut s'avérer très pratique dans le cas où on ne connaitrait pas à l'avance les conditions d'une requête LINQ par exemple. Le premier intérêt d'esql est le même.
Cependant, il y en a un autre : certaines requêtes très simples en sql peuvent être compliquées en LINQ. Prenons un exemple :
Soit la table TAs avec, entre autre, une colonne Name de type nvarchar NOT NULL.
Je peux très facilement écrire :
SELECT Name FROM TAs WHERE Name LIKE 'a%z'
En LINQ, je vais faire :
from ta in context.TAs
where ta.Name.StartsWith("a") && ta.Name.EndsWith("z")
select ta.Name;
Jusque là, facile. Attention cependant, si j'avais voulu tester LIKE 'a%a', il aurait fallu que je teste également ta.Name != "a".
La requête SQL générée à partir de la requête LINQ To Entities précédente sera la suivante :
SELECT
Extent1.Name AS Name
FROM dbo.TAs AS Extent1
WHERE ((CAST(CHARINDEX(N'a', Extent1.Name) AS int)) = 1) AND ((RIGHT(Extent1.Name, CAST(LEN(N'z') AS int))) = N'z')
Avec LINQ To SQL, on aura ceci :
exec sp_executesql N'SELECT t0.Name
FROM dbo.TAs AS t0
WHERE (t0.Name LIKE @p0) AND (t0.Name LIKE @p1)',N'@p0 nvarchar(2),@p1 nvarchar(2)',@p0=N'a%',@p1=N'%z'
Il est intéressant de constater que lorsqu'on exécute ces deux requêtes, et qu'on regarde le plan d'exécution, la première a un Cached plan size plus petit mais un Estimated Number of Rows plus important. Cependant, dans tous les cas, le plan est le même : un SELECT suivi d'un Clustered Index Scan.
Passons maintenant à une requête "plus compliqué au moins côté LINQ" :
SELECT Name FROM TAs WHERE Name LIKE '%a%z%'
On pourrait comparer les index mais le problème est que si on a un z avant le a, on pourra avoir un texte LIKE '%a%z%' comme zaza et ne pas l'avoir avec l'index. J'ai alors pensé à utiliser IndexOf("a") < LastIndexOf("z") mais LastIndexOf n'est pas traduisible en SQL.
Du coup, je m'en sort avec ceci :
from ta in context.TAs
let indexOfA = ta.Name.IndexOf("a")
where indexOfA != -1 && ta.Name.Substring(indexOfA + 1).Contains("z")
select ta.Name;
traduit par
SELECT
Extent1.Name AS Name
FROM dbo.TAs AS Extent1
WHERE (-1 <> ((CAST(CHARINDEX(N'a', Extent1.Name) AS int)) - 1)) AND ((CAST(CHARINDEX(N'z', SUBSTRING(Extent1.Name, ((CAST(CHARINDEX(N'a', Extent1.Name) AS int)) - 1) + 1 + 1, (LEN(Extent1.Name)) - (((CAST(CHARINDEX(N'a', Extent1.Name) AS int)) - 1) + 1))) AS int)) > 0)
via LINQ To Entities et par
exec sp_executesql N'SELECT t1.Name
FROM (
SELECT t0.Name,
(CASE
WHEN (DATALENGTH(@p0) / 2) = 0 THEN 0
ELSE CHARINDEX(@p0, t0.Name) - 1
END) AS value
FROM dbo.TAs AS t0
) AS t1
WHERE (t1.value <> @p1) AND ((
(CASE
WHEN (DATALENGTH(@p2) / 2) = 0 THEN 0
ELSE CHARINDEX(@p2, t1.Name) - 1
END)) < (
(CASE
WHEN (DATALENGTH(@p3) / 2) = 0 THEN (CONVERT(Int,DATALENGTH(t1.Name) / 2)) - 1
WHEN CHARINDEX(@p3, t1.Name) = 0 THEN -1
ELSE 1 + ((CONVERT(Int,DATALENGTH(t1.Name) / 2)) - ((CONVERT(Int,DATALENGTH(@p3) / 2)) + CHARINDEX(REVERSE(@p3), REVERSE(t1.Name))))
END)))',N'@p0 nvarchar(1),@p1 int,@p2 nvarchar(1),@p3 nvarchar(1)',@p0=N'a',@p1=-1,@p2=N'a',@p3=N'z'
via LINQ To SQL.
Dans ce cas, la requête LINQ To Entities est quasi équivalente à la requête SQL "classique" à l'exception de l'Estimated Number Rows et de façon infinitésimale de Estimated Operator Cost (0,0000005 au lie de 0). En revanche, et c’est curieux, la requête LINQ To SQL, pourtant prévu pour être optimisée pour SQL Server est moins performante. De plus, elle n'a pas le même plan d'exécution : on trouve un Filter entre le SELECT et le Clustered Index Scan.
Quoi qu'il en soit, dans cet exemple, on voit bien qu'on perd en lisibilité du code avec LINQ. Ceci aurait pu être encore pire avec LIKE ‘%a%z%e%r%t%y%’ :
from ta in context.TAs
let indexOfA = ta.Name.IndexOf("a")
where indexOfA != -1
let sA = ta.Name.Substring(indexOfA + 1)
let indexOfZ = sA.IndexOf("z")
where indexOfZ != -1
let sZ = sA.Substring(indexOfZ + 1)
let indexOfE = sZ.IndexOf("e")
where indexOfE != -1
let sE = sZ.Substring(indexOfE + 1)
let indexOfR = sE.IndexOf("r")
where indexOfR != -1
let sR = sE.Substring(indexOfR + 1)
let indexOfT = sR.IndexOf("t")
where indexOfT != -1 && sR.Substring(indexOfT + 1).Contains("y")
select ta.Name;
D'où l'intérêt d'esql :
Ainsi, la requête esql suivante :
context.CreateQuery<TA>("SELECT VALUE ta FROM TestEntities.TAs as ta WHERE ta.Name LIKE '%a%z%e%r%t%y%'")
génèrera la requête SQL suivante :
SELECT
Extent1.Id AS Id,
Extent1.Name AS Name
FROM dbo.TAs AS Extent1
WHERE Extent1.Name LIKE '%a%z%e%r%t%y%'
Maintenant ce qui serait bien, ça serait de mixer des requêtes LINQ To Entities avec des requêtes Esql. C'est à l'étude pour les futures versions, cependant, ce n'est pas possible avec la v1 (qui devrait sortir en juin). Il faut donc passer par LINQ To Object pour répondre à ce cas grâce à l'extension method AsEnumerable() sur la requêtes LINQ To Entities.
Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
