Hi There

The getdbcount method gives diffrent count . I am getting something else wthi the collection count for e.g. dbcount gives 14 while there is originallly 9 records inthe collection here is my code

ClientCollection oClientCollection = new ClientCollection();
            RelationCollection oRelationCollection = new RelationCollection();

            IPredicateExpression filter = new PredicateExpression();
            filter.Add(ClientRatingFields.RatedByClientId == clientId);
            if (rating != null)
            {
                filter.AddWithAnd(ClientRatingFields.Rating == rating);
            }

            IPrefetchPath oPrefatchPath = new PrefetchPath(EntityType.ClientEntity);
            
            IPredicateExpression filterForClientRatings = new PredicateExpression();
            filterForClientRatings.Add(ClientRatingFields.RatedByClientId == clientId);
            relations.Add(ClientEntity.Relations.ClientRatingEntityUsingRatedClientId, JoinHint.Left);
            oPrefatchPath.Add(ClientEntity.PrefetchPathClientRatings_, 0, filterForClientRatings);
            
            IPredicateExpression filterForClientFavourite = new PredicateExpression();
            filterForClientFavourite.Add(ClientFavoriteFields.ClientId == clientId);
            relations.Add(ClientEntity.Relations.ClientFavoriteEntityUsingFavoriteClientId, JoinHint.Left);
            oPrefatchPath.Add(ClientEntity.PrefetchPathClientFavorites_, 0, filterForClientFavourite);
            
            oRelationCollection.Add(ClientEntity.Relations.SearchBlackListEntityUsingBlackListedClientId, JoinHint.Left);
            IPredicateExpression oPredicateExpress = new PredicateExpression();
            oPredicateExpress.Add(SearchBlackListFields.BlackListedByClientId == clientId);
            oPrefatchPath.Add(ClientEntity.PrefetchPathSearchBlackLists_, 0, oPredicateExpress);

            IPredicateExpression oPredicateExpression = new PredicateExpression();
            oPredicateExpression.Add(new FieldCompareSetPredicate(ClientFields.ClientId, ClientRatingFields.RatedClientId, SetOperator.In, filter, false));
            oPredicateExpression.Add(ClientFields.IsActive != false);
            oPredicateExpression.AddWithAnd(ClientFields.IsDeleted != true);

            ISortExpression oSortExpresssion = new SortExpression();
            oSortExpresssion.Add(ClientFields.FirstName | SortOperator.Ascending);
            oSortExpresssion.Add(ClientFields.ClientId | SortOperator.Ascending);
            GetSortExpressionForClientRating(sortOrder, oSortExpresssion);

            totalRated = oClientCollection.GetDbCount(oPredicateExpression, oRelationCollection);
            oClientCollection.GetMulti(oPredicateExpression, 0, oSortExpresssion, oRelationCollection, oPrefatchPath, pageNumber, pageSize);

here above totalRated differs with the collections count ( if query fired without any paging) I am unable to get what's wrong with it.

Could it be that you are limiting the fetch of the collection by providing paging parameters. (page size & page number).

No no I have metioned it earlier(in green letters in previous message) that if i don't provide any kind of page number and page size still the counts are different

Please check the generated SQL queries and run them manually against the database, and see if the outputs are the same as you have with code.

If yes, then there must be something wrong with the SQL queries. And so I'd ask you to post them over here.

I tried to get the query through oPrediacteExpression.ToQueryText(); But it threw an exception with the message "DatabaseSpecificCreator object not set. Cannot create query part." what's wrong with it?

Check the troubleshoot sectiong of the docs, to know how to get the generated SQL.

Do I need to create a database specific creator to get the generated sql query??

No. Please read this: Dynamic Query Engine tracing

Thanks buddy

I got this generated query

( [MaterMatchMakersOld].[dbo].[Clients].[ClientID] IN (SELECT [MaterMatchMakersOld].[dbo].[ClientRating].[RatedClientID] AS [RatedClientId] FROM [MaterMatchMakersOld].[dbo].[ClientRating]   WHERE ( [MaterMatchMakersOld].[dbo].[ClientRating].[RatedByClientID] = @p10)) AND [MaterMatchMakersOld].[dbo].[Clients].[IsActive] <> @p11 AND [MaterMatchMakersOld].[dbo].[Clients].[IsDeleted] <> @p12)

when I executed this query in sql server

(select  * from clients where  [MaterMatchMakersOld].[dbo].[Clients].[ClientID] IN (SELECT [MaterMatchMakersOld].[dbo].[ClientRating].[RatedClientID] AS [RatedClientId] FROM [MaterMatchMakersOld].[dbo].[ClientRating]   WHERE ( [MaterMatchMakersOld].[dbo].[ClientRating].[RatedByClientID] = 21695)) AND [MaterMatchMakersOld].[dbo].[Clients].[IsActive] !=0  AND [MaterMatchMakersOld].[dbo].[Clients].[IsDeleted] !=1)

I get 59 records which is correct. But getdbcount returned 7331 which is far away from the actual count.

chirag.vashisht wrote:

( [MaterMatchMakersOld].[dbo].[Clients].[ClientID] IN (SELECT [MaterMatchMakersOld].[dbo].[ClientRating].[RatedClientID] AS [RatedClientId] FROM [MaterMatchMakersOld].[dbo].[ClientRating]   WHERE ( [MaterMatchMakersOld].[dbo].[ClientRating].[RatedByClientID] = @p10)) AND [MaterMatchMakersOld].[dbo].[Clients].[IsActive] <> @p11 AND [MaterMatchMakersOld].[dbo].[Clients].[IsDeleted] <> @p12)

Something is missing. That is not the complete query. Please check that.

chirag.vashisht wrote:

I get 59 records which is correct. But getdbcount returned 7331 which is far away from the actual count.

I think this is because the Distinct and joins. Please use your Tracing in level 4 and post the full sql generated.

As a workaround please try to use GetScalar with AggregateFunction.CountDistinct.

Hi there I found out the solution remove the relations from the dbcount method

I am getting an exception with comapare range predicate

{SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() 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.ExecuteScalar() at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.ExecuteScalar()

the predicate expression is as follows

FieldCompareRangePredicate oFieldCompareRangePredicate = new FieldCompareRangePredicate(ClientFields.ZipCode, false, GetZipCodesForSearch(Convert.ToInt32(oSearchCriteriaDto.AreaRange), Convert.ToInt32(oSearchCriteriaDto.AreaZipCode)).ToArray());
                        oPredicateExpression.AddWithAnd(oFieldCompareRangePredicate);

Here above the GetZipCodesForSearch returns near about 2300 counts which I think exceedes the capacity of an comapre range predicate. Kindly tell what can be done with it?

This is a different issue than the GetDBCount(), or is it related to the GetDBCount() result being different? If it's a different issue, then please create a new thread for it.