GQ08 VI: l’équivalent de ‘in’ avec Linq to Sql


Linq to Sql est capable de générer le ‘in’ Sql. Cela se traduit en Linq par l’usage de la méthode .Contains(). L’exemple ci-dessous compile mais pourtant plante à l’exécution.

Pourquoi donc ?

var db = new NorthwindDataContext(); var cq = CompiledQuery.Compile((NorthwindDataContext ctx, string[] cities) => from c in ctx.Customers where cities.Contains(c.City) select c); var list = cq(db, new string[] {"Paris", "London"}).ToList();
Comments (8)

  1. La CompiledQuery génère lors du Compile la requête SQL a executer. Par conséquent, elle a besoin de connaitre l’ensemble des éléments du tableau.

    Pour s’en convaincre, il suffit de faire le test suivant :

    var cq =

       CompiledQuery.Compile((NorthwindDataContext ctx) =>

           from c in ctx.Customers

           where new string[] { "Paris", "London" }.Contains(c.City)

           select c);

    foreach (var c in cq(db).Where(c => c.City == "Paris"))

       Console.WriteLine(c.CompanyName);

    Dans ce cas, la requête SQL générée sera

    SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]

    FROM [dbo].[Customers] AS [t0]

    WHERE [t0].[City] IN (@p0, @p1)’,N’@p0 nvarchar(5),@p1 nvarchar(6)’,@p0=N’Paris’,@p1=N’London’

    et le Where se fera donc côté client.

    En revanche, si on utilise une Expression sans CompiledQuery :

    Expression<Func<NorthwindDataContext, string[], IQueryable<Customer>>> exp = (ctx, cities) =>

       from c in db.Customers

       where cities.Contains(c.City)

       select c;

    var f = exp.Compile();

    foreach (var c in f(db, new string[] { "Paris", "London" }).Where(c => c.City == "Paris"))

       Console.WriteLine(c.CompanyName);

    la requête générée sera la suivante :

    SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]

    FROM [dbo].[Customers] AS [t0]

    WHERE ([t0].[City] = @p0) AND ([t0].[City] IN (@p1, @p2))’,N’@p0 nvarchar(5),@p1 nvarchar(5),@p2 nvarchar(6)’,@p0=N’Paris’,@p1=N’Paris’,@p2=N’London’

    cqfd

  2. Olivier says:

    Il me semble qu’une requête qui a des parametres de type liste ne compile pas car l’opération de compilation dépend du nombre d’éléments dans la ou les listes passées en parametre.

    En version non précompilée la requête fonctionne d’ailleurs.

    En revanche je ne connais de solution simple pour contourner le problème, j’attends avec impatience ta solution !

  3. Simon says:

    C’est la précompilation qui foire, car elle est sensée générer une SqlCommand avec un nombre finit de paramêtres, et un CommandText fixe qui sera utilisée à chaque éxécution. Le soucis avec les tableaux passés en paramètres, c’est qu’on à un nombre de SqlParameter qui change en fonction de la taille du tableau, et que le texte de la commande change aussi.

    Je vois un seul endroit où il y a une feinte, et encore j’ai pas testé depuis la RC, c’est lorsqu’on fait un Take(myVariable) : Sous SQL 2005+, la commande générée est paramétée (SELECT TOP(@0) … ), mais sous SQL 2000, le TOP() ne peut pas prendre de variable externe en paramètre, et c’est donc le texte de la commande qui est modifié. C’est à vérifier, mais il me semble que le seul cas de modification dynamique du CommandText supporté par les CompiledQuery est justement celui là (mais là encore, ca date de la RC ou même beta 2)

  4. mitsu says:

    Deux bonnes réponses.

    En effet, le but des CompiledQueries est de conserver le sql généré lors du premier appel afin de ne pas faire la conversion ‘expression’ vers ‘sql’ à chaque appel. C’est un moyen très efficace de gagner en performance avec Linq to Sql, surtout sur les requêtes complexes.

    Ce mécanisme à cependant une limitation, le ‘in’ sql ne peut pas être supporté.

    En effet, le nombre de paramètres fera varier le sql avec un ‘in (..,..)’ ayant plusieurs valeurs.

    Ce sql ne peut donc pas être factorisé.

    Cette limitation n’est pas propre à Linq, c’est évidemment la même chose avec des requêtes paramétrées ou même des procédures stockées qui ne peuvent pas non plus factoriser un ‘in’ et ses paramètres.

    Olivier désolé mais pas de solution :p

    La seule chose que l’on peut faire si le nombre de paramètres de la liste est fixe est:

    var cq2 =

       CompiledQuery.Compile((NorthwindDataContext ctx, string city1, string city2) =>

           from c in ctx.Customers

           where new string[] { city1, city2}.Contains(c.City)

           select c);

    var list = cq2(db, "Paris", "London").ToList();

    …comme on pourrait le faire avec une procédure stockée.

    Mention spéciale pour Matthieu qui a posté qu’un seul commentaire de taille raisonnable. :p

  5. Simon says:

    Tiens, le miens est pas passé :/ c’est dommage, ca parlait d’une exception ou la CompiledQuery modifiait le code SQL factorisé (cas à la con du TOP sous SQL 2000)

  6. Olivier says:

    Hélas je me doutais qu’il n’existait pas de solution miracle…

    On pourrait d’ailleurs être tenté "d’externaliser" la liste au lieu de la passer en paramètre. Mais en raison des closures cela ne fonctionne pas, bien entendu…

    exemple de modification du code :

    var db = new NorthwindDataContext();

    db.Log = Console.Out;

    var Cities = new[] {"Paris", "London"};

    var cq =

      CompiledQuery.Compile((NorthwindDataContext ctx) =>

        from c in ctx.Customers

        where (Cities.Contains (c.City))

        select

        new {cust = c, hash = Cities.GetHashCode()});

        var result = cq(db);

    foreach (var c in result)

    {Console.WriteLine(c.cust.CustomerID+" "+c.hash);}

    Cities = new[] { "Paris", "London", "Sao Paulo" };

    result = cq(db);

    foreach (var c in result)

    {Console.WriteLine(c.cust.CustomerID+" "+c.hash);}

    La sortie console montre bien que c’est l’objet Cities existant au moment de la compilation qui est pris en compte quelle que soit sa redéfinition ultérieure :

    SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[Contact

    itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Cou

    try], [t0].[Phone], [t0].[Fax]

    FROM [dbo].[Customers] AS [t0]

    WHERE [t0].[City] IN (@p0, @p1)

    — @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Paris]

    — @p1: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]

    — Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

    AROUT 39860949

    BSBEV 39860949

    CONSH 39860949

    EASTC 39860949

    NORTS 39860949

    PARIS 39860949

    SEVES 39860949

    SPECD 39860949

    SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[Contact

    itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Cou

    try], [t0].[Phone], [t0].[Fax]

    FROM [dbo].[Customers] AS [t0]

    WHERE [t0].[City] IN (@p0, @p1)

    — @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Paris]

    — @p1: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]

    — Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

    AROUT 39860949

    BSBEV 39860949

    CONSH 39860949

    EASTC 39860949

    NORTS 39860949

    PARIS 39860949

    SEVES 39860949

    SPECD 39860949

    La seconde requête est identique à la première, et l’objet Cities utilisé est bien le même malgré sa redéfinition (hash code identique à sa première définition).

    En plaçant la liste dans une table temporaire qu’on utiliserait dans la requête cela fonctionnerait, mais on repousse le problème : dans un contexte multiutilisateur et multihtread cette table prend du temps à gérer. Avec deux colonnes on peut s’en sortir : code utilisateur / item, les deux étant marqués comme PK. On peut dès lors n’avoir qu’une table pour tous les utilisateurs.

    Mais il faut avouer que c’est loin d’être idéal et que le temps perdu à une telle gestion ne se justifie certainement plus et n’est certainement plus compensé par le gain de temps espéré par la compilation de la requête…

  7. mitsu says:

    Désolé à tous, j’avais activé la modération :).

    Elle n’y est plus désormais.

    Oui Olivier, la solution la plus "base de données" serait en effet de remplir une table temporaire avec une sorte d’ID de transaction et de s’en servir pour injecter les valeurs du ‘in’.

    S’il n’y a pas beaucoup de données c’est en effet un effort pour pas grand chose. S’il y en a beaucoup ce n’est pas choquant, les moteurs de bases de données utilisent eux-mêmes en interne une multitude de tables temporaires ne serait-ce que pour les transactions.

    Simon, en effet il est fort possible que ton cas soit identique et fasse échouer une compiled query. Il faudrait tester. Qui a Sql2000 :p ?