GroupBy/Count query fails because it tries to select all columns

[GoogleCodeExporter]

A LINQ expression that tries to do a simple GroupBy/Count query fails,
because it tries to select all columns.

What steps will reproduce the problem?
1. Generate a code file from a Postgres database. In my case there is a
"countries" table with an "income_group" column.
2. Execute the following code:

var countriesByGroup = db.Countries.GroupBy(c => c.IncomeGroup);
foreach (var cg in countriesByGroup)
{
    Console.WriteLine("{0}: {1}", cg.Key ?? "NULL", cg.Count());
}

What is the expected output? What do you see instead?

Expected the call to succeed and enumerate IGrouping objects of keys and
their counts, but an exception is throw instead:

Npgsql.NpgsqlException: ERROR: 42803: column "countries.country_code" must
appear in the GROUP BY clause or be used in an aggregate function
   at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext()
   at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject()
   at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
   at Npgsql.ForwardsOnlyDataReader.NextResult()
   at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration,
CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock
threadBlock, Boolean synchOnReadError)
   at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at
DbLinq.Data.Linq.Sugar.Implementation.QueryRunner.Select[T](SelectQuery
selectQuery) in C:\Documents and Settings\evgenyp\My Documents\Visual
Studio
2008\Projects\dblinq\src\DbLinq\Data\Linq\Sugar\Implementation\QueryRunner.cs:li
ne
70
   at DbLinq.Data.Linq.Implementation.QueryProvider`1.GetEnumerator() in
C:\Documents and Settings\evgenyp\My Documents\Visual Studio
2008\Projects\dblinq\src\DbLinq\Data\Linq\Implementation\QueryProvider.cs:line
216
   at ConsoleApplication1.Program.TryDbLinq(IDbConnection conn) in
C:\Documents and Settings\evgenyp\My Documents\Visual Studio
2008\Projects\ConsoleApplication1\Program.cs:line 114
   at ConsoleApplication1.Program.LinqToPostgres() in C:\Documents and
Settings\evgenyp\My Documents\Visual Studio
2008\Projects\ConsoleApplication1\Program.cs:line 63
   at ConsoleApplication1.Program.Main(String[] args) in C:\Documents and
Settings\evgenyp\My Documents\Visual Studio
2008\Projects\ConsoleApplication1\Program.cs:line 22

What version of the product are you using? On what operating system?

SVN revision 1234 on .NET 3.5, Windows XP Pro x64 SP2 with PostgreSQL 8.3.1.

Original issue reported on code.google.com by emp....@gmail.com on 14 Oct 2009 at 6:30

[GoogleCodeExporter]

Test case PostgreSql_test.ReadTest.C4_CountWithOrderBy() looks like it covers 
this issue.

Original comment by emp....@gmail.com on 19 Oct 2009 at 5:04

[GoogleCodeExporter]

Workaround: call .ToList() before enumerating the results (and optionally
AsQueryable() if you need an IQueryable, eg.

var countriesByGroup = db.Countries.GroupBy(c => 
c.IncomeGroup).ToList().AsQueryable();

Original comment by emp....@gmail.com on 19 Oct 2009 at 6:52

[GoogleCodeExporter]

PostgreSql_test.ReadTest.C4_CountWithOrderBy() doesn't cover it.

What covers it is ReadTest_GroupBy.G02_SimpleGroup_First(), which does 
effectively the same thing (but 
adds a .First() call).  The exception under SQL Server is the same, or close 
enough:

Test_NUnit_MsSql.ReadTest_GroupBy.G02_SimpleGroup_First:
System.Data.SqlClient.SqlException : Column 'dbo.Customers.CustomerID' is 
invalid in the select list 
because it is not contained in either an aggregate function or the GROUP BY 
clause.

What's interesting is the SQL that Linq-to-SQL produces:

SELECT TOP (1) [t1].[City] AS [Key]
FROM (
    SELECT [t0].[City]
    FROM [dbo].[Customers] AS [t0]
    GROUP BY [t0].[City]
    ) AS [t1]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 
3.5.30729.4926

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], 
[t0].[ContactTitle], [t0].[Address], 
[t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], 
[t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE ((@x1 IS NULL) AND ([t0].[City] IS NULL)) OR ((@x1 IS NOT NULL) AND 
([t0].[City] IS NOT NULL) 
AND (@x1 = [t0].[City]))
-- @x1: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Aachen]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 
3.5.30729.4926

The 'SELECT TOP' is from the .First() call, and can be ignored; what's key is 
that we have two SQL 
calls generated for the .GroupBy() LINQ expression, the first SQL expression to 
get the key, and the 
second one to perform the query with the given key.

I don't know if DbLinq currently has support for doing interelated SQL calls in 
this fashion...

Original comment by jonmpr...@gmail.com on 21 Jan 2010 at 10:30

• Changed state: Accepted
• Added labels: Type-Defect, Component-DbLinq, Priority-Medium

[GoogleCodeExporter]

Please, try out the solution on the link below:
http://code.google.com/p/dblinq2007/issues/detail?id=132#c2

Original comment by tos.oliv...@gmail.com on 1 Apr 2012 at 1:16