I need to use CountColumn with distinct as Count alone is getting it wrong because of some joins.

My query has a rather complex join in it. The nested SQL statement generated has an invalid list of columns in it's select list. Running just Count or enumerating the result works (doesn't throw errors).

SD.LLBLGen.Pro.LinqSupportClasses.NET35 - 2.6.10.202

SD.LLBLGen.Pro.ORMSupportClasses.NET20 - 2.6.9.1202

THis is the output:

exec sp_executesql N'SELECT TOP 1 COUNT(DISTINCT [LPA_L1].[KnowledgebaseId]) AS [LPAV_] FROM (SELECT [ClientPortal].[dbo].[Knowledgebase].[KnowledgebaseId], [ClientPortal].[dbo].[Knowledgebase].[Subject], [ClientPortal].[dbo].[Knowledgebase].[ArticleStatus], [ClientPortal].[dbo].[Knowledgebase].[DisplayOrder], [ClientPortal].[dbo].[Knowledgebase].[Summary], [ClientPortal].[dbo].[Knowledgebase].[Content], [ClientPortal].[dbo].[Knowledgebase].[PublishDate], [ClientPortal].[dbo].[Knowledgebase].[ExpiryDate], [ClientPortal].[dbo].[Knowledgebase].[IsInternal], [ClientPortal].[dbo].[Knowledgebase].[IsAvailableEveryone], [ClientPortal].[dbo].[Knowledgebase].[CreatedById], [ClientPortal].[dbo].[Knowledgebase].[ViewCount], [ClientPortal].[dbo].[Knowledgebase].[CategoryId], [ClientPortal].[dbo].[Knowledgebase].[UpdatedOn], [ClientPortal].[dbo].[Knowledgebase].[UpdatedBy], [ClientPortal].[dbo].[Knowledgebase].[CreatedOn], [ClientPortal].[dbo].[Knowledgebase].[CreatedBy] FROM (SELECT [LPA_L3].[KnowledgebaseId], [LPA_L3].[Subject], [LPA_L3].[ArticleStatus], [LPA_L3].[DisplayOrder], [LPA_L3].[Summary], [LPA_L3].[Content], [LPA_L3].[PublishDate], [LPA_L3].[ExpiryDate], [LPA_L3].[IsInternal], [LPA_L3].[IsAvailableEveryone], [LPA_L3].[CreatedById], [LPA_L3].[ViewCount], [LPA_L3].[CategoryId], [LPA_L3].[UpdatedOn], [LPA_L3].[UpdatedBy], [LPA_L3].[CreatedOn], [LPA_L3].[CreatedBy] FROM ((((( [ClientPortal].[dbo].[Knowledgebase] [LPA_L3] LEFT JOIN [ClientPortal].[dbo].[KnowledgebaseProject] [LPA_L4] ON [LPA_L3].[KnowledgebaseId] = [LPA_L4].[KnowledgebaseId]) LEFT JOIN [ClientPortal].[dbo].[ProjectClientUser] [LPA_L5] ON [LPA_L4].[ProjectId] = [LPA_L5].[ProjectId]) LEFT JOIN [ClientPortal].[dbo].[KnowledgebaseDepartment] [LPA_L6] ON [LPA_L3].[KnowledgebaseId] = [LPA_L6].[KnowledgebaseId]) LEFT JOIN [ClientPortal].[dbo].[Project] [LPA_L7] ON [LPA_L6].[DepartmentId] = [LPA_L7].[DepartmentId]) LEFT JOIN [ClientPortal].[dbo].[ProjectClientUser] [LPA_L8] ON [LPA_L7].[ProjectId] = [LPA_L8].[ProjectId]) WHERE ( ( ( ( [LPA_L5].[ClientUserId] = @ClientUserId1) AND ( (CASE WHEN CASE WHEN ( ( ( [LPA_L3].[PublishDate] IS NULL) OR ( [LPA_L3].[PublishDate] <= @PublishDate3)) OR ( [LPA_L3].[ExpiryDate] IS NOT NULL)) THEN 1 ELSE 0 END=1 THEN CASE WHEN ( [LPA_L3].[ExpiryDate] >= @ExpiryDate4) THEN 1 ELSE 0 END ELSE @LO15 END)=1))))) [LPA_L2] WHERE ( ( ( ( ( ( [LPA_L2].[ArticleStatus] = @ArticleStatus6)) AND ( [LPA_L2].[IsInternal] = @IsInternal7)))))) [LPA_L1]',N'@ClientUserId1 int,@PublishDate3 datetime,@ExpiryDate4 datetime,@LO15 int,@LPFA_242 bit,@ArticleStatus6 nvarchar(32),@IsInternal7 bit',@ClientUserId1=93,@PublishDate3='2010-06-10 00:00:00:000',@ExpiryDate4='2010-06-10 00:00:00:000',@LO15=1,@LPFA_242=1,@ArticleStatus6=N'Published',@IsInternal7=0

LinqMetaData meta = new LinqMetaData(Adapter);

        var list = from c in meta.Knowledgebase
                   select c;

        if (contactId.HasValue)
            list = from c in list
                   join kp in meta.KnowledgebaseProject on c.KnowledgebaseId equals kp.KnowledgebaseId into tmpKP
                   from kpJoin in tmpKP.DefaultIfEmpty()
                   join pcu in meta.ProjectClientUser on kpJoin.ProjectId equals pcu.ProjectId into tmpPCU
                   from pcuJoin in tmpPCU.DefaultIfEmpty()
                   //
                   join kd in meta.KnowledgebaseDepartment on c.KnowledgebaseId equals kd.KnowledgebaseId into tmpKD
                   from kdJoin in tmpKD.DefaultIfEmpty()
                   join p in meta.Project on kdJoin.DepartmentId equals p.DepartmentId into tmpP
                   from pdJoin in tmpP.DefaultIfEmpty()
                   join pcuAlt in meta.ProjectClientUser on pdJoin.ProjectId equals pcuAlt.ProjectId into tmpPCUALT
                   from pcuAltJoin in tmpPCUALT.DefaultIfEmpty()
                   //where (pcuJoin.ClientUserId == contactId.Value || pcuAltJoin.ClientUserId == contactId.Value)
                   where (pcuJoin.ClientUserId == contactId.Value)
                    && (c.PublishDate == null || c.PublishDate <= DateTime.Today || c.ExpiryDate != null ? c.ExpiryDate >= DateTime.Today : true)
                   select c;

        if (!string.IsNullOrEmpty(status))
            list = list.Where(p => p.ArticleStatus == status);

        if (isInternal.HasValue)
            list = list.Where(p => p.IsInternal == isInternal.Value).OrderByDescending(kb => kb.KnowledgebaseId);

        return list;

So we can't use Count() atm as it's returning an incorrect (what we consider incorrect) number and we can't use CountColumn as can't seem to handle the query.

we can't use CountColumn as can't seem to handle the query.

Please describe what goes wrong when use CountColumn, if there is an exception and a stack trace, please post them. Also please post the generated SQL code in that case, stating what you need to modify.

Sorry, I suppose the error would be useful!

The SQL is I have already posted is what is emitted, I can't get the exact error message until this afternoon when other team members get to work.

But basically was complaining (SqlException) that it couldn't resolve the item in the select list in the outermost nested query. The first item is: [ClientPortal].[dbo].[Knowledgebase].[KnowledgebaseId]

I believe it should be LPAV_1].[KnowledgebaseId] or something like that.

Server Error in '/' Application.

The multi-part identifier "ClientPortal.dbo.Knowledgebase.KnowledgebaseId" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.Subject" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.ArticleStatus" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.DisplayOrder" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.Summary" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.Content" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.PublishDate" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.ExpiryDate" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.IsInternal" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.IsAvailableEveryone" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.CreatedById" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.ViewCount" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.CategoryId" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.UpdatedOn" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.UpdatedBy" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.CreatedOn" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.CreatedBy" could not be bound. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The multi-part identifier "ClientPortal.dbo.Knowledgebase.KnowledgebaseId" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.Subject" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.ArticleStatus" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.DisplayOrder" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.Summary" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.Content" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.PublishDate" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.ExpiryDate" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.IsInternal" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.IsAvailableEveryone" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.CreatedById" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.ViewCount" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.CategoryId" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.UpdatedOn" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.UpdatedBy" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.CreatedOn" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.CreatedBy" could not be bound.

**Stack Trace: **

[SqlException (0x80131904): The multi-part identifier "ClientPortal.dbo.Knowledgebase.KnowledgebaseId" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.Subject" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.ArticleStatus" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.DisplayOrder" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.Summary" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.Content" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.PublishDate" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.ExpiryDate" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.IsInternal" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.IsAvailableEveryone" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.CreatedById" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.ViewCount" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.CategoryId" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.UpdatedOn" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.UpdatedBy" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.CreatedOn" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.CreatedBy" could not be bound.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1953274 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4849707 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33 System.Data.SqlClient.SqlDataReader.get_MetaData() +96 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10 SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +288

[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "ClientPortal.dbo.Knowledgebase.KnowledgebaseId" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.Subject" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.ArticleStatus" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.DisplayOrder" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.Summary" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.Content" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.PublishDate" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.ExpiryDate" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.IsInternal" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.IsAvailableEveryone" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.CreatedById" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.ViewCount" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.CategoryId" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.UpdatedOn" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.UpdatedBy" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.CreatedOn" could not be bound. The multi-part identifier "ClientPortal.dbo.Knowledgebase.CreatedBy" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.] SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +485 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) +50 RWA.Data.DataAccessAdapter.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in D:\Projects\Shivam\Shivam Reference Web Application\Main\Source\Shivam RWA v1.0\app\RWA.Data\DataAccessAdapter.cs:292 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary2 typeConvertersToRun) +70 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) +336 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) +612 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) +610 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) +63 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) +41 SD.LLBLGen.Pro.LinqSupportClasses.QueryableExtensionMethods.CountColumn(IQueryable1 source, Expression1 selector, Boolean applyDistinct) +211 Shivam.ASL.Core.Services.DomainService1.PageResult(IQueryable1 result, IPageState pageState) in D:\Projects\Shivam\Shivam ASL\Main\Source\Shivam ASL v1.0\app\Shivam.ASL.Core\Services\DomainService.cs:82 RWA.DomainServices.KnowledgebaseService.List(Nullable1 contactId, String subject, String status, Nullable1 categoryId, Nullable1 lastNDays, Nullable1 isInternal, IPageState pageState) in D:\Projects\Shivam\Shivam Reference Web Application\Main\Source\Shivam RWA v1.0\app\RWA.DomainServices\KnowledgebaseService.cs:69 RWA.Web.ClientUIControllers.HomeController.Index() in D:\Projects\Shivam\Shivam Reference Web Application\Main\Source\Shivam RWA v1.0\app\RWA.Web\ClientUIControllers\HomeController.cs:66 lambda_method(ExecutionScope , ControllerBase , Object[] ) +74 System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +17 System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) +178 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) +24 System.Web.Mvc.<>c__DisplayClassd.<InvokeActionMethodWithFilters>b__a() +52 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func1 continuation) +254 System.Web.Mvc.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c() +19 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func1 continuation) +254 System.Web.Mvc.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c() +19 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList1 filters, ActionDescriptor actionDescriptor, IDictionary2 parameters) +192 System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +314 System.Web.Mvc.Controller.ExecuteCore() +105 System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +39 System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +7 System.Web.Mvc.<>c__DisplayClass8.<BeginProcessRequest>b__4() +34 System.Web.Mvc.Async.<>c__DisplayClass1.<MakeVoidDelegate>b__0() +21 System.Web.Mvc.Async.<>c__DisplayClass81.<BeginSynchronous>b__7(IAsyncResult _) +12 System.Web.Mvc.Async.WrappedAsyncResult`1.End() +59 System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +44 System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +7 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +8678910 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

**SQL code **

sp_executesql N'SELECT TOP 1 COUNT(DISTINCT [LPA_L1].[KnowledgebaseId]) AS [LPAV_] FROM (SELECT [ClientPortal].[dbo].[Knowledgebase].[KnowledgebaseId], [ClientPortal].[dbo].[Knowledgebase].[Subject], [ClientPortal].[dbo].[Knowledgebase].[ArticleStatus], [ClientPortal].[dbo].[Knowledgebase].[DisplayOrder], [ClientPortal].[dbo].[Knowledgebase].[Summary], [ClientPortal].[dbo].[Knowledgebase].[Content], [ClientPortal].[dbo].[Knowledgebase].[PublishDate], [ClientPortal].[dbo].[Knowledgebase].[ExpiryDate], [ClientPortal].[dbo].[Knowledgebase].[IsInternal], [ClientPortal].[dbo].[Knowledgebase].[IsAvailableEveryone], [ClientPortal].[dbo].[Knowledgebase].[CreatedById], [ClientPortal].[dbo].[Knowledgebase].[ViewCount], [ClientPortal].[dbo].[Knowledgebase].[CategoryId], [ClientPortal].[dbo].[Knowledgebase].[UpdatedOn], [ClientPortal].[dbo].[Knowledgebase].[UpdatedBy], [ClientPortal].[dbo].[Knowledgebase].[CreatedOn], [ClientPortal].[dbo].[Knowledgebase].[CreatedBy] FROM (SELECT [LPA_L3].[KnowledgebaseId], [LPA_L3].[Subject], [LPA_L3].[ArticleStatus], [LPA_L3].[DisplayOrder], [LPA_L3].[Summary], [LPA_L3].[Content], [LPA_L3].[PublishDate], [LPA_L3].[ExpiryDate], [LPA_L3].[IsInternal], [LPA_L3].[IsAvailableEveryone], [LPA_L3].[CreatedById], [LPA_L3].[ViewCount], [LPA_L3].[CategoryId], [LPA_L3].[UpdatedOn], [LPA_L3].[UpdatedBy], [LPA_L3].[CreatedOn], [LPA_L3].[CreatedBy] FROM ((((( [ClientPortal].[dbo].[Knowledgebase] [LPA_L3] LEFT JOIN [ClientPortal].[dbo].[KnowledgebaseProject] [LPA_L4] ON [LPA_L3].[KnowledgebaseId] = [LPA_L4].[KnowledgebaseId]) LEFT JOIN [ClientPortal].[dbo].[ProjectClientUser] [LPA_L5] ON [LPA_L4].[ProjectId] = [LPA_L5].[ProjectId]) LEFT JOIN [ClientPortal].[dbo].[KnowledgebaseDepartment] [LPA_L6] ON [LPA_L3].[KnowledgebaseId] = [LPA_L6].[KnowledgebaseId]) LEFT JOIN [ClientPortal].[dbo].[Project] [LPA_L7] ON [LPA_L6].[DepartmentId] = [LPA_L7].[DepartmentId]) LEFT JOIN [ClientPortal].[dbo].[ProjectClientUser] [LPA_L8] ON [LPA_L7].[ProjectId] = [LPA_L8].[ProjectId]) WHERE ( ( ( ( [LPA_L5].[ClientUserId] = @ClientUserId1) AND ( (CASE WHEN CASE WHEN ( ( ( [LPA_L3].[PublishDate] IS NULL) OR ( [LPA_L3].[PublishDate] <= @PublishDate3)) OR ( [LPA_L3].[ExpiryDate] IS NOT NULL)) THEN 1 ELSE 0 END=1 THEN CASE WHEN ( [LPA_L3].[ExpiryDate] >= @ExpiryDate4) THEN 1 ELSE 0 END ELSE @LO15 END)=1))))) [LPA_L2] WHERE ( ( ( ( ( ( [LPA_L2].[ArticleStatus] = @ArticleStatus6)) AND ( [LPA_L2].[IsInternal] = @IsInternal7)))))) [LPA_L1]',N'@ClientUserId1 int,@PublishDate3 datetime,@ExpiryDate4 datetime,@LO15 int,@LPFA_242 bit,@ArticleStatus6 nvarchar(32),@IsInternal7 bit',@ClientUserId1=93,@PublishDate3='2010-06-10 00:00:00:000',@ExpiryDate4='2010-06-10 00:00:00:000',@LO15=1,@LPFA_242=1,@ArticleStatus6=N'Published',@IsInternal7=0

adsolanki has the error message.

Would you please try using Distinct().Count(), as in the following example:

var q = from p in metaData.Products
                    from o in metaData.OrderDetails                 
                    select o;

var q1 = q.Select(o => o.OrderId).Distinct().Count();

which implements:

SELECT COUNT(DISTINCT OrderID) FROM[Order Details] od
INNER JOIN Products p ON p.ProductID = od.ProductID

Cool thanks Walaa.