Execute multiple queries using SqlKata

Question

Is is it possible to execute multiple queries using SqlKata at the same time.

Currently I have the following code:

var latestUsersQuery = db.Query("tbl_users").Where("created_at", ">", from);
var activitiesQuery = db.Query("tbl_activities").WhereIn("user_id", latestUsersQuery.Clone());

and I am querying each one alone.

var latestUsers = latestUsersQuery.Get();
var activities = activitiesQuery().Get();

I am wondering if there is a way to execute both queries at the same time.

// for example something like this
var results = db.GetAll(latestUsersQuery, activitiesQuery);

Thanks.

Is your goal to send only one query to the database (to enhance the performance)? — Mohammed Noureldin, May 01 '18 at 15:55

Cory · Answer 1 · 2018-08-01T17:27:13.013

Late to the game on this, but you could extend SqlKata's QueryFactory class similar to how they write the other methods; using extension methods.

using SqlKata;
using SqlKata.Execution;
using System.Collections.Generic;
using System.Linq;
// ...

public static class QueryFactoryExtensions
{
    public static IEnumerable<IEnumerable<T>> GetAll<T>(this QueryFactory db, params Query[] queries)
    {
        return queries.Select(q =>
        {
            return SqlKata.Execution.QueryFactoryExtensions.Get<T>(db, q);
        });
    }
    public static IEnumerable<IEnumerable<dynamic>> GetAll(this QueryFactory db, params Query[] queries)
    {
        return GetAll<dynamic>(db, queries);
    }
}

The usage would look like:

var query1 = new Query();
var query2 = new Query();

var results = db.GetAll(query1, query2)

From there you could: enumerate over it, cast to an array and access by index, etc.

Bear in mind here, this is not sending one query to the database as mentioned in the comments of the OP. If performance is truly what you're after, then you might look at using the Dapper extension method QueryMultiple instead of Query (which is what SqlKata is calling on the Get() methods). I'm not familiar enough with the underlying magic that Dapper is doing to be able to confidently give you direction. Another possiblity for you could be to add extensions for GetAsync instead of Get. This may be better suited to your needs.

All of that said, at minimum I hope that this provides you with some direction in which to make a decision or potentially benchmark performance.

Execute multiple queries using SqlKata

1 Answers1