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