'Get raw sql string in Sql Kata

I am using SqlKata purely to build sql queries in C#. I am wanting to take the output of my built up Query, get the raw (compiled) sql string, and execute it against SQL.

I thought this would do it:

var factory = new QueryFactory(null, new SqlServerCompiler());
var query = new Query();
...
var sqlText = factory.Compiler.Compile(query).Sql;

But this gives this:

SELECT TOP (@p0) [AllStarFull].[GameNumber], [AllStarFull].[LeagueId], [AllStarFull].[PlayedInGame] FROM [AllStarFull]

This throws an exception because (@p0) is a param, and not the actual value.

In the documentation, it mentions to bring in Logger but I don't really need logging functionality (right now).

https://sqlkata.com/docs/execution/logging

var db = new QueryFactory(connection, new SqlServerCompiler());

// Log the compiled query to the console
db.Logger = compiled => {
    Console.WriteLine(compiled.ToString());
};

var users = db.Query("Users").Get();

Is there anyway to get the raw sql string from the Query with all params populated?



Solution 1:[1]

If you need just to build the SQL there is no need to include the SqlKata.Execution package (which is include the QueryFactory class).

The simplest way is:

using SqlKata;
using SqlKata.Compilers;

// Create an instance of SQLServer
var compiler = new SqlServerCompiler();

var query = new Query("Users").Where("Id", 1).Where("Status", "Active");

SqlResult result = compiler.Compile(query);

string sql = result.Sql;
List<object> bindings = result.Bindings; // [ 1, "Active" ]

as mentioned in the docs you can use the result.ToString() to get the full query

var sql = result.ToString();

but this is not a good practice, the correct way is to use the parameterized query with bindings to execute it.

taken from https://sqlkata.com/docs#compile-only-example

Solution 2:[2]

If you are injecting QueryFactory dependency, you can use its compiler:

var query = new Query("Users")... etc

var rawSQL = queryFactory.Compiler.Compile(query).RawSql;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 amd
Solution 2 Oleksiy