- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Count Distinct in LLBLGen
Joined: 20-Mar-2008
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(List
1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary
2 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.LLBLGenProQuery
1.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(*))
Joined: 20-Mar-2008
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.