Is there a way to manipulate the existing query in SQLKata?

Question

I am looking for a flexible Query Generator for C# that will work without all of the overhead of a full ORM. I am thinking of using SQL Kata, but I have a use case that I am not sure is covered:

In our application, we build "base" queries which do the original select from the tables, and then use that as a sub-query upon which we perform Grouping and other analytics. When we build those base queries, we don't yet know which exact fields we will actually need in our final output, so we include all fields in the select list.

What I am looking for is a way to review the final query, and then use the actual select list to limit which fields are being retrieved by the base query. This will provide performance benefits for our columnar store.

Is there a way to traverse and modify the query in SQL Kata?
Something like - Show me all of the fields in the outer query, show me all of the fields in the inner "base" query", and then let me modify the inner select list.

Answer 1

The short answer is yes, you can inspect the query instance and loop over the selected columns, something like this:

var columns = query.GetComponents<AbstractColumn>("select");

foreach(var column in columns)
{
    if(column is Column col)
    {
        Console.WriteLine(col.Name);

    } elseif(column is RawColumn raw) {
        Console.WriteLine(col.Expression);
    } elseif(column is QueryColumn subQuery) {
        // do something with subQuery.Query
    }    
}

but what I suggest is to build the base query without selecting any column, and at a later phase select the needed columns only.

Note that at the compiling phase if you don't select any column SqlKata will translate this to select * which is convenient in your case.

var baseQuery = new Query("TableOrViewHere as Report")

// at a later phase for example in the controller
// select the columns dynamically
var innerQuery = baseQuery.Clone().Select(Request.Columns);

var data = db.Query()
    .From(innerQuery.As("inner"))
    .Get();