AsCount is broken when Distinct is used

[fairking]

Hi all,

I am encountering an issue when Distinct is being used with AsCount. Any idea how to solve that issue? Or maybe the way how the AsCount() should work must be different when Distinct is being involved?

For example the following query throws a sql error The column 'Id' was specified multiple times:

var query = new Query("Users")
    .Distinct()
    .Select("Users.Gender", "Roles.Name")
    .Join("Roles", "Users.Role", "Roles.Id")
    .AsCount();

[fairking]

Ok, I've found that providing a list of columns back to the query solves the problem. Can someone explain why do we replace a list of columns with * when we do AsCount. Just trying to understand whether there is a catch.

My solution was to include all the select columns back to the query. I hope it could help others with the same issue.

var query = new Query("Users")
    .Distinct()
    .Select("Users.Gender", "Roles.Name")
    .Join("Roles", "Users.Role", "Roles.Id");

query.AsCount(query.GetComponents<Column>("select").Select(x => x.Name).ToArray());

As a result I created a helper method which allows to use AsCount in such way:

        public static Query AsCountWithColumns(this Query query)
        {
            if (query.IsDistinct)
            {
                var components = query.GetComponents("select");

                var columns = components.Select(x =>
                    x is RawColumn
                        ? (x as RawColumn).Expression
                        : (x as Column).Name
                    ).ToArray();

                return query.AsCount(columns);
            }
            else
            {
                return query.AsCount();
            }
        }