How to compare result of two subqueries in where clause SQLKata

Question

I try to compare results of two subqueries in where clause in SQLKata.

In SQL it should be like this:

WHERE (SELECT count(id) FROM main.someTable) = (SELECT count(id) FROM main.anotherTable)

In SQLKata I can compare result of subquery with scalar value:

var mainSubquery = new Query("main.someTable")
            .SelectRaw("count(id)");

var anotherSubquery = new Query("main.anotherTable")
            .SelectRaw("count(id)");

query
    .WhereSub(mainSubquery, "=", 0)

But I can't compare results of two subqueries this way:

query
    .WhereSub(mainSubquery, "=", anotherSubquery),

How can I fix it? Maybe I should execute both of the subqueries and only then compare their results?

Answer 1

No overload accepts queries on both the left and right sides together.

But you can always compile the queries and use the WhereRaw. One gotcha is to be aware of the bindings orders.

Take a look on this example:

using SqlKata;
using SqlKata.Compilers;

var sub1 = new Query("A").SelectRaw("count(1)");
var sub2 = new Query("B").SelectRaw("count(1)");

var compiler = new SqlServerCompiler();
var c1 = compiler.Compile(sub1);
var c2 = compiler.Compile(sub2);
var bindings = c1.Bindings;
bindings.AddRange(c2.Bindings); // order (c1, c2) is important here

var query = new Query("Table").WhereRaw($"({c1.Sql}) = ({c2.Sql})", bindings);

var result = compiler.Compile(query);
Console.WriteLine(result.ToString());

This will output the following

SELECT * FROM [Table] WHERE (SELECT count(1) FROM [A]) = (SELECT count(1) FROM [B])