Multiple bindings with the same value

[fairking]

I am trying to get some query working which includes binding value multiple times:
eg.

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

Is there any way to modify query in a way, so the DateTime.Today declared only ones.

Doc says nothing about that.

[ahmad-moussawi]

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.

[fairking]

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")

[michelbieleveld]

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 ;)