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?

Running into these limits isn't really common, only in cases where people have a long list of values which they try to use as an IN clause it goes wrong. I'd suggest to look at the code which produces these long lists and see if that can be optimized: - by using a subquery on the db which also contains the same values - by going to the source of these values and use ranges with between predicates - by fetching the data in batches.

Related threads: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=14490 http://llblgen.com/tinyforum/Messages.aspx?ThreadID=11462 http://llblgen.com/tinyforum/Messages.aspx?ThreadID=6705

I could not found any helpful solution. All the parameters are critical as the search is done on the basis of zip codes kindly help me out!

This is a database limitation that you have to work around it. Please re-visit the solutions David (daelmo) has explained.

By the way how does "oSearchCriteriaDto.AreaZipCode" get populated?

GetZipCodesForSearch(Convert.ToInt32(oSearchCriteriaDto.AreaRange) ,Convert.ToInt32(oSearchCriteriaDto.AreaZipCode))

In GetZipCodesForSearch method we pass AreaRange say 50 miles and a Zipcode respectively. The method returns a list of zip codes those were within 50 miles range of that particular zip code

In GetZipCodesForSearch method we pass AreaRange say 50 miles and a Zipcode respectively. The method returns a list of zip codes those were within 50 miles range of that particular zip code

One of the solutions was: Instead of querying the database to return a list of ZipCodes to be used in another query. Incorporate this query as a subQuery or Join to it, in your main query.

For example you can let GetZipCodesForSearch return a DerivedTableDefinition so you can join to in the main query.

I read further and found a nested query case also be helpful in this can for that I was making an expression which looks with C# variables like this

 var R = 6371; // km
                        Double dLat = (originalLatitude - searchLatitude) / (180 / Math.PI);
                        Double dLon = (originamLongitude - searchLongitude) / (180 / Math.PI);
                        Double a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) +
                                   Math.Cos(searchLatitude / (180 / Math.PI)) * Math.Cos(originalLatitude / (180 / Math.PI)) *
                                   Math.Sin(dLon / 2) * Math.Sin(dLon / 2);
                        Double c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a));
                        return (R * c);

I could reach upto only following state

IExpression oExpressionDLat = new Expression(oZipCodeEntity.Latitude, ExOp.Sub, ZipCodeFields.Latitude);
                        oExpressionDLat = new Expression(oExpressionDLat, ExOp.Div, 180 / Math.PI);
                        IExpression oExpressionDLon = new Expression(oZipCodeEntity.Longitude, ExOp.Sub, ZipCodeFields.Longitude);
                        oExpressionDLon = new Expression(oExpressionDLon, ExOp.Div, 180 / Math.PI);

My problem here is that how do I use sine, cos, and sqrt functions with the expressions

chirag.vashisht wrote:

My problem here is that how do I use sine, cos, and sqrt functions with the expressions

Transact-SQL provides Math functions as well: http://msdn.microsoft.com/en-us/library/ms177516.aspx So you can use LLBLGen DBFunctionCalls Example:

IExpression cosExp = new DBFunctionCall("COS", new object[]{ OZipCodeEntity.Longitude });

As mentioned in my post above, if you can't convert it to a subquery, you always can perform it by batches: first 999, then another 999, and so on.

In batches?? could you please elaborate....

In batches?? could you please elaborate....

Instead of fetching entities and filtering using a range of 2300 parameters. Fetch more than once, first time using first 1000 parameters and second time using the rest 1300 parameters. Merge both resultSets into the same collection.

I can not do that as am using paging

oClientCollection.GetMulti(oPredicateExpression, 0, oSortExpression, relations, oPrefatchPath, pageNumber, pageSize);

Here merging will give wrong data by paging for e.g. Total records matching the criteria are 1 2 3 4 5 6 7 8 9 10

now for page size 5 and for pageNumber 1 the total records coming without batches will be : 1 2 3 4 5 Now in batches for the page number 1 records are batch1 batch 2 1 6 2 7
3 8 4 9 5 10

This is a totally wrong scenerio

chirag.vashisht wrote:

This is a totally wrong scenerio

Mmm, ok. Then use the subquery approach.