Invalid SQL when using COUNT with DISTINCT

[err0x1a]

Invalid SQL is generated when using COUNT in conjunction with DISTINCT for MSSQL.

For Instance code:
q.From("my_table").Distinct().AsCount()

Will generate:
SELECT COUNT(DISTINCT *) AS [count] FROM [my_table]

I would be grateful if you could prioritize a fix.

[ceastwood]

@AsyncError

stackoverflow

For now, you can use the following to achieve what you want

Query object:

var q = new Query()
                .From(subQuery => subQuery.Distinct().From("my_table"), "computed")
                .AsCount();

Generated SQL:

SELECT COUNT(*) AS [count] FROM (SELECT DISTINCT * FROM [my_table]) AS [computed]

[ahmad-moussawi]

We will use this approach when selecting * or multiple columns or when we have a group by in the query.

Example with group by:

select count(1) from (select client_id, max(balance) from accounts group by client_id)

[err0x1a]

@ceastwood Valuable information about the Count, thanks.

The example I provided was just to report the problem. But in my code the error occurs when Paginate is used. My query is more complex, I'm using INTERSECT for multiple results, then it also has a JOIN, and I also use the same query for Paginate and ForPage.

[ceastwood]

@AsyncError Sorry, I meant to get back to this earlier--

Is there any way that you can mock up the query or a good piece of it? You don't need to provide the real tables or columns.

I did some tests earlier with intersects and joins on the part of the query that generates the distinct values so am just trying to see if I was thinking along the right track before providing another example.

Let me know sir,

[ceastwood]

@AsyncError I am also going to try to put together an example for with the Paginate and ForPage

[ceastwood]

@AsyncError
I've tried to show a contrived example of how to make things work for now-- and if you would still like to provide the structure of your query I can help try and adapt it. That being said, the workaround isn't exactly clean 😄. Personally, on queries like this I would be generating a temp table with a sort key and query the pagination from that table.

The following example should return 2 rows as-is, and if you uncomment the where statement on the distinctQuery it will reduce to 1, showing the join and where filter. The overall of the filtering is to find rows that start with c and end with n with their ColumnA existing in ColumnATypes

Lastly, I wrote out the queries a little verbosely to try and show the pieces.

SQL Test Schema & Data:

USE [scratch]; --need to update for your test database
SET NOCOUNT ON;

IF OBJECT_ID('[dbo].[BaseTable]') IS NULL
BEGIN

CREATE TABLE [dbo].[BaseTable] (BaseTableId int PRIMARY KEY IDENTITY(1,1), ColumnA int, ColumnB int, ColumnC nvarchar(50))

INSERT INTO [dbo].[BaseTable] (ColumnA, ColumnB, ColumnC) VALUES (10, 100, 'Red') --1
INSERT INTO [dbo].[BaseTable] (ColumnA, ColumnB, ColumnC) VALUES (10, 101, 'Maroon') --2
INSERT INTO [dbo].[BaseTable] (ColumnA, ColumnB, ColumnC) VALUES (10, 102, 'Scarlet') --3
INSERT INTO [dbo].[BaseTable] (ColumnA, ColumnB, ColumnC) VALUES (10, 103, 'Crimson') --4
INSERT INTO [dbo].[BaseTable] (ColumnA, ColumnB, ColumnC) VALUES (10, 103, 'Crimson') --5 --this duplicate is on purpose to show the distinct filtering

INSERT INTO [dbo].[BaseTable] (ColumnA, ColumnB, ColumnC) VALUES (20, 100, 'Blue') --6
INSERT INTO [dbo].[BaseTable] (ColumnA, ColumnB, ColumnC) VALUES (20, 101, 'Cobalt') --7
INSERT INTO [dbo].[BaseTable] (ColumnA, ColumnB, ColumnC) VALUES (20, 102, 'Cyan') --8
INSERT INTO [dbo].[BaseTable] (ColumnA, ColumnB, ColumnC) VALUES (20, 103, 'Teal') --9

END

IF OBJECT_ID('[dbo].[ColumnATypes]') IS NULL
BEGIN
	CREATE TABLE [dbo].[ColumnATypes] ([ColumnA] int NOT NULL, [Description] nvarchar(50))
	INSERT INTO [dbo].[ColumnATypes] VALUES (10, 'Reds')
	INSERT INTO [dbo].[ColumnATypes] VALUES (20, 'Blues')
END

The (not-so-pretty) query setup:

IDbConnection conn = new SqlConnection("Data Source=xxxSomeDatabaseServerxxx;Integrated Security=True;Initial Catalog=scratch");
var qf = new QueryFactory(conn, new SqlServerCompiler());

var filterQuery = new Query("dbo.BaseTable")
    .Select("ColumnA", "ColumnB")
    .Intersect(with => with.Select("ColumnA", "ColumnB")
        .From("dbo.BaseTable")
        .WhereLike("ColumnC", "C%"))
    .Intersect(with => with.Select("ColumnA", "ColumnB")
        .From("dbo.BaseTable")
        .WhereLike("ColumnC", "%n"));
                

var distinctQuery = new Query()
    .From(filterQuery, "source")
    .Select("source.ColumnA","source.ColumnB")
    .Join("dbo.ColumnATypes", "source.ColumnA", "ColumnATypes.ColumnA")
        //.Where("ColumnATypes.Description", "Blues") //uncomment to bring total count to 1, removes the 'Reds' result
    .Distinct();

var workaroundQuery = new Query()
    .Select("*")
    .From(distinctQuery, "workaround");

var xquery = qf.FromQuery(workaroundQuery);
var result = xquery.Paginate(1, 1);

Tested against a live sql2016 server

[err0x1a]

@ceastwood I appreciate your dedicated time in trying a solution, it worked perfectly.

I was not understanding how I could do this, and this last part was decisive to make my queries work.

var workaroundQuery = new Query()
    .Select("*")
    .From(distinctQuery, "workaround");

Thanks man.

I'm passionate about how this library works.
I can not imagine ever dealing with huge string of queries again.