SqlServerCompiler removing 'GROUP BY' clause

[DavidWever]

Reopening the issue found here because it is still an issue and I don't have permissions to reopen the other issue thread. I've copied the latest post from my coworker @areevesat below:

I have a use case where this issue is impacting my solution. I construct a query counting the number of organization rows that have one or more "org_storage" records. If the user has specified to search by name, I narrow this down by name.

totalQuery = new Query("org_storage")
    .Select("organization_id")
    .LeftJoin("organizations", "org_storage.organization_id", "organizations.id")
    .GroupBy("organization_id")
    .AsCount();
if (!string.IsNullOrWhiteSpace(searchParams.Name))
{
    totalQuery.WhereContains("name", searchParams.Name);
}

I would expect this to generate SQL comparable to the below

SELECT count(*) FROM (SELECT organization_id FROM org_storage LEFT JOIN organizations ON organizations.id = org_storage.organization_id WHERE name LIKE '%query%' GROUP BY organization_id)
Now, it might be better to construct this as one query and then use that as an inner query and use .AsCount() on the outer query (which is the workaround I ended up using). But it took a long time debugging this, digging around into the generated SQL commands, and 3 developers on and off looking at this before we figured out what was going on. It would make much more sense for the compiler to either generate the code as written or throw an error message that it can't be compiled. Silently removing part of the query runs very counter to user expectations.