Search code examples
sql-servervb.netsqlkata

Using SqlKata from an existing Query

I have existing queries used for jobs that run within services to generate reports. Simple things like

"Select * from Transactions"

The jobs will then append parameters to these queries based on preset rules, like Date>Yesterday etc. SqlKata looks like it can do this but I'm not sure how to instantiate the Query object from an existing query. Is something like this possible?

Dim Qry as new Query("Select * from Transactions").OrderByDesc("Date")
Qry.Where("Date", ">", Date.Now().AddDays(-1))
return Qry.Get()
Solution

  • The closest thing that you can do in this case is to wrap the inner query and add conditions on top of it, you can use the SubQuery or CTE approach here. Something like this, this in C# but the idea is the same.

    var existingSql = "select * from transactions";
var query = new Query().FromRaw($"({existingSql}) as inner")
                       .Where("date", ">=", DateTime.UtcNow.Date);

    checkout this example on playground