Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

Abonnements

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 :

Publié mercredi 19 mars 2008 07:49 par Matthieu MEZIL

Classé sous : , , ,

Commentaires

# re: Pourquoi ESQL c'est bien ? @ mercredi 19 mars 2008 12:23

Bien entendu, avec LINQ To Object, on aurait pu utiliser Regex.IsMatch mais ce n'est pas traduisible en SQL dans le cadre d'une requête LINQ To SQL ou LINQ To Entities.

Matthieu MEZIL

# re: Pourquoi ESQL c'est bien ? @ mercredi 19 mars 2008 13:22

Interessant :) Cela confirme ce que je pensais de linq to sql, utile pour des requêtes ultra simple, pour des requêtes plus complexe, il faut impérativement passer par du sql ...

Dans le premier cas tu n'indique pas quel requête sql est généré avec quoi. La première c'est la requête généré avec linq to entities ?

-----

Quand tu fais le createQuery il te retoure un IQueryable ? donc tu as accès à l'arbre d'expression ? si oui : c'est cool on va pouvoir s'amuser.

Par exemple :

from o in context.CreateQuery&lt;&gt;("...")

where ...

select o.XXX

Cependant tu dis qu'il n'est pas encore possible de mixer les 2, j'en conclu que ca ne fonctionnera pas, et que peut être même on n'a pas accès à l'expression ...

Avoir accès à l'expression nous permettrait aussi de générer du C# à partir de SQL ... :p

cyril

# re: Pourquoi ESQL c'est bien ? @ mercredi 19 mars 2008 14:54

"Dans le premier cas tu n'indique pas quel requête sql est généré avec quoi. La première c'est la requête généré avec linq to entities ? " Oui merci, c'est corrigé.

"Quand tu fais le createQuery il te retoure un IQueryable ?" Oui, il me retourne un ObjectQuery<T> qui implémente IQueryable<T>.

"donc tu as accès à l'arbre d'expression ? " Oui

"Cependant tu dis qu'il n'est pas encore possible de mixer les 2, j'en conclu que ca ne fonctionnera pas, et que peut être même on n'a pas accès à l'expression ... " Regarde http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3025689&amp;SiteID=1

Matthieu MEZIL

# re: Pourquoi ESQL c'est bien ? @ mercredi 19 mars 2008 18:43

VB supporte le traditionnelement mot clé LIKE.

Je suppose donc que le Linq to SQL de VB le supporte aussi Wink

Mais j'ai jamais testé, quelqu'un peut confirmer ?

FREMYCOMPANY

# re: Pourquoi ESQL c'est bien ? @ jeudi 20 mars 2008 02:33

Effectivement Fremy. Par contre avec LINQ VB, il faut utiliser des * à la place des % :

From ta in context.TAs _

Where ta.Name Like "*a*z*e*r*t*y*" _

Select ta.Name

Matthieu MEZIL

# re: Pourquoi ESQL c'est bien ? @ jeudi 20 mars 2008 14:47

Sauf que en fait, non : Like n'est pas reconnu par LINQ To Entities.

Matthieu MEZIL

Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Merci par Blog de Jérémy Jeanson le 10-01-2019, 20:47

- Office 365: Script PowerShell pour auditer l’usage des Office Groups de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 11:02

- Office 365: Script PowerShell pour auditer l’usage de Microsoft Teams de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 10:39

- Office 365: Script PowerShell pour auditer l’usage de OneDrive for Business de votre tenant par Blog Technique de Romelard Fabrice le 04-25-2019, 15:13

- Office 365: Script PowerShell pour auditer l’usage de SharePoint Online de votre tenant par Blog Technique de Romelard Fabrice le 02-27-2019, 13:39

- Office 365: Script PowerShell pour auditer l’usage d’Exchange Online de votre tenant par Blog Technique de Romelard Fabrice le 02-25-2019, 15:07

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Stream Portal par Blog Technique de Romelard Fabrice le 02-21-2019, 17:56

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Video Portal par Blog Technique de Romelard Fabrice le 02-18-2019, 18:56

- Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis par Blog Technique de Romelard Fabrice le 01-28-2019, 16:13

- SharePoint Online: Script PowerShell pour désactiver l’Option IRM des sites SPO non autorisés par Blog Technique de Romelard Fabrice le 12-14-2018, 13:01