11

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?

mwilson
  • 12,295
  • 7
  • 55
  • 95
  • Ah. Not documented, but after sniffing the object a little more, I can just do this: `factory.Compiler.Compile(query).ToString();` – mwilson Jun 13 '19 at 02:18
  • Just curious why you don't use it to execute the queries also ? – amd Jun 13 '19 at 09:08
  • I'm using Entity Framework for it. I'm not sure which direction to go right now with that part. I was hoping entity framework had some way to build dynamic queries but didn't find a good way to do it. SqlKata looked interesting as it gave me a way to build out my query from my custom configuration model. I might explore dropping entity framework and replace with sqlkata. Just kind of exploring things right now. – mwilson Jun 13 '19 at 15:16

2 Answers2

18

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

amd
  • 20,637
  • 6
  • 49
  • 67
  • 2
    Yes, I agree. I really do not want to do the raw sql string and would rather leverage params. Just trying my brute force/poc first. I'm liking SQLKata's ease of building up sql queries in an object oriented/dynamic way. Good work, @amd! – mwilson Jun 14 '19 at 01:31
1

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

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

var rawSQL = queryFactory.Compiler.Compile(query).RawSql;
Oleksiy
  • 37,477
  • 22
  • 74
  • 122