Multiple bindings with the same value #666

Activity

1. ahmad-moussawi commented on Apr 7, 2023

   

   ahmad-moussawi

   on Apr 7, 2023

   Last edited by ahmad-moussawi

   Contributor

   More actions

   There is a hidden feature that allows you to define a variable and use it multiple times.

   using static SqlKata.Expressions;
   
   var query = new Query("Users")
   .Define("Today", DateTime.Today)
   .Where("CreatedAt", Variable("Today"))
   .Where("PublishedAt", ">", Variable("Today"));

   Check the example here.

   This is not publicly documented since it may be changed or removed later, so consider this if you decide to use it.
2. fairking commented on Apr 13, 2023

   

   fairking

   on Apr 13, 2023

   Last edited by fairking

   Author

   More actions

   Thanks for the answer. Would it be correct?

   var query = new Query("Users")
   .Define("Today", DateTime.Today)
   .OrderByRaw("CASE WHEN StartDate < ? and (EndDate is null or EndDate > ?) THEN 0 WHEN StartDate > ? THEN 1 ELSE 2 END",
       bindings: new object[] { Variable("Today"), Variable("Today"), Variable("Today") })

   It doesn't make sense to me.

   Would be nice to have named parameters, so I can use them in raw queries like this:

   var query = new Query("Users")
   .Define("Today", DateTime.Today)
   .WhereRaw("DATEPART('weekday', @Today) = 3")
   .OrderByRaw("CASE WHEN StartDate < @Today and (EndDate is null or EndDate > @Today) THEN 0 WHEN StartDate > @Today THEN 1 ELSE 2 END")

   ❤️React with ❤️1Reacted by Marc Lewandowski
3. michelbieleveld commented on Apr 8, 2025

   

   michelbieleveld

   on Apr 8, 2025

   More actions

   Sorry for bringing this up two years later, but having the same issue and after consideration also think it doesn't make sense. The expectation of a variable is that it would be named in the query, and importantly also only needed to be bound once. This is relevant in, for example, cursor based pagination where one would do something like this

   SELECT *
   FROM Users
   WHERE
         (LastName > 'Smith')
      OR (LastName = 'Smith' AND FirstName < 'John')
      OR (LastName = 'Smith' AND FirstName = 'John' AND Id > 42)
   ORDER BY
       LastName ASC,
       FirstName DESC,
       Id ASC
   OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY;

   where the current solution would require the parameters:

    smith, smith, john,smith,john,42 
   

   while with named variables we would just have

   smith, john, 42
   

   which perhaps don't make that much of a practical difference, but I do think the latter is the expected implementation. Or maybe I am just missing something ;)
4. areevesat commented on Jan 8, 2026

   

   areevesat

   on Jan 8, 2026

   Last edited by areevesat

   More actions

   I would definitely like to see something like this. Ideally, I could write SqlKata code like

   var query = new Query("Users")
   .WhereRaw("first_name = ?0 OR last_name = ?0 OR email = ?1", searchParams.name, searchParams.Email);

   And have this converted into an appropriate query. Right now, with just the question marks, I have to pass the same parameter to the method twice.