Hi,

I am trying to achieve the effect similar to this.

SELECT City, COUNT (DISTINCT FirstName) AS CityCount
FROM Employees
GROUP BY City

And here is the LINQ query that I came up with.

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                LinqMetaData mdata = new LinqMetaData(adapter);
                var results = (from o in mdata.Employees
                                    group o by o.City into g
                                    select new {City = g.Key, CityCount = (from p in g select p.FirstName).Distinct().Count()} );

                foreach (var r in results)
                {
                    Console.WriteLine(string.Format("{0}: {1}", r.City, r.CityCount ));
                }
            }

But I got an exception like this:

POC.LinqToLLBL.HelloLinq.MyFirstTest: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException : An exception was caught during the execution of a retrieval query: Invalid column name 'FirstName'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ----> System.Data.SqlClient.SqlException : Invalid column name 'FirstName'.

at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) at NW26.DatabaseSpecific.DataAccessAdapter.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in C:\dev\Mainline\POC\DataAccess\NW\DatabaseSpecific\DataAccessAdapter.cs:line 292 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary2 typeConvertersToRun) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() at POC.LinqToLLBL.HelloLinq.MyFirstTest() in C:\dev\Mainline\POC\LINQToLLBL\HelloLinq.cs:line 28 --SqlException at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)

Please help! The code runs on top of NorthWind database.

Thanks!

There's a special extension method for this: CountColumn(field, bool). The error also suggests you are using an older build.

docs wrote:

CountColumn(field [, bool applyDistinct]). This method can count on a field and if true is passed for applyDistinct, the count will be a Count distinct. Currently Count() is a CountRow (which is a Count(*))

Thanks for the tip.

CountColumn is almost exactly what I am looking for, except it applies only to IQueryable not IGroupping.

So I still cannot use it as the built in Count extension method on the group variable.

But thanks anyway, I now should be able to make it work.