Error with AsCount on query with Common Table Expression

[Wimmen]

When you have a query with a Common Table Expression the query.AsCount().Get() function will return "Invalid object name 'cte0'."

When I print the SQL of query.AsCount() the WITH block is missing.

[fairking]

There is another issue with AsCount (https://github.com/sqlkata/querybuilder/discussions/749) maybe we can try to rethink what would be the best way to count rows.

[Wimmen]

This is the way I do counts in my application:

var countQuery = QueryFactory.Query();
countQuery.SelectRaw("count(1) as Count").From(query, "csq0");
foreach (var with in withs)
{
     countQuery = countQuery.WithRaw(with.Alias, with.Query);
}

var result = await countQuery.GetAsync();

So wrapping the original query in a select count(1) and reapplying the Common Table Expressions to the new query if there are any.