How to compile complex queries with SqlKata first and execute later

Question

I want to create and compile a SQL query using SqlKata and then in some other function execute this query. It's not obvious based on the documentation how this is done, especially for complex queries.

I have tried converting the query to a string and then running it, but this does not seem to be the right thing to do and the string does not compile well for complex queries.

    public class TradeLoader : ITradeLoader
    {

        public SqlResult CreateTradeQuery(Target targets, Group groups, DateTime fromTime)
        {
            var compiler = new SqlServerCompiler();
            var query = new Query().FromRaw(@"
                    [trades] AS t WITH (NOLOCK)
                    LEFT JOIN [info] AS i WITH (NOLOCK)
                    ON t.Id = i.Id
                ")
                .Select("i.Name", "t.Price", "t.Volume")
                .WhereTime("t.CreatedDate", ">", fromTime);

            var subQuery = new Query(@"
                    [trades] AS t WITH (NOLOCK)
                    LEFT JOIN [info] AS i WITH (NOLOCK)
                    ON t.Id = i.Id
                ");
            foreach (var target in targets)
            {
                subQuery.OrWhere(q => q.WhereIn("i.Name", groups.Keys).WhereIn("i.GroupName", target.Value));
            }

            query.Where(subQuery);
            SqlResult result = compiler.Compile(query);
            return result; // How to execute this somewhere else?
        }
    }

It's not clear to me how I can execute this query somewhere else, either using the SqlKata.Execution or System.Data.SqlClient. Preferably I should be able to run it in both.

Answer 1

This is probably basic knowledge for those who are proficient in C#/.NET, but what I wanted to do was to inject params into the SQL query created with SqlKata and then run it without using SqlKata. In the end, I did it in the following way (and there are probably other ways to do this too):

public class TradeLoader : ITradeLoader
{
    ...

    public async Task<SomeDataType> RunQuery()
    {
        ...
        var sqlCommand = CreateTradeQuery(someTargets, someGroups, someDateTime);
        using (SqlConnection sql = new SqlConnection(_connectionString))
        {
            sql.Open();
            var command = new SqlCommand(sqlCommand.Sql, sql) {CommandTimeout = _timeout};
            foreach (var (name, value) in sqlCommand.NamedBindings)
            {
                var parameter = command.CreateParameter();
                parameter.ParameterName = name;
                parameter.Value = value;
                command.Parameters.Add(parameter);
            }

            using (SqlDataReader dataReader = await command.ExecuteReaderAsync())
            {
                while (await dataReader.ReadAsync())
                {
                    // read data here
                }
            }
        }
        return data;
    }
}

This allows you to build your queries with SqlKata, but run the query using other libraries like in this example where I use System.Data.SqlClient.