Hi,

I am currently using the LLBLGen Pro Runtime Framework Version v5.10.2.

My application has a generic search functionality for a database table (which is itself based on a LLBLGen typed list). When the user enters a search term, the GUI Framework (DevExpress in our case) will translate this to a search query that compares every column of the typed list to the value. We in turn use some very generic logic to convert this to a LLBLGen Query with a RelationPredicateBucket.

Part of our code looks something like this:

   OrmSupportClasses.Predicate BuildEqualsPredicate(int columnIndex, object value)
   {
        EntityFieldCore entityField = some logic that uses the columnIndex to extract the EntityFieldCore from the typed list;
        return new FieldCompareValuePredicate(entityField, Nothing, ComparisonOperator.Equal, value, negate);
   }

Where the value parameter is the search term that has been parsed and casted to its most likely type (meaning Int, if it's an integer).

This has worked fine (it has for years now) until I started getting ORMQueryExecutionExceptions because my users started searching for Integer values on a typed list that has a very short numeric column (numeric(3, 0) to be exact). If they search for the integer value 1234, then the above code will be called, and it produces the following:

       new FieldCompareValuePredicate(entityField, Nothing, ComparisonOperator.Equal, 1234, negate);

This leads to a ORMQueryExecutionException when the query is executed.

         Message: An exception was caught during the execution of a retrieval query: Parameter value '1234' is out of range.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

         StackTrace: 
            at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.ExecuteScalar() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.10\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 144
            at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.ExecuteScalarQuery(IRetrievalQuery queryToExecute) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.10\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 532
            at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.GetDbCount(IEntityFields2 fields, IRelationPredicateBucket filter, IGroupByCollection groupByClause, Boolean allowDuplicates) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.10\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 330
            at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass18_0.<GetDbCount>b__0() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.10\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 391
            at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategy[T](Func`1 toExecute) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.10\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 918
            at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.GetDbCount(IEntityFields2 fields, IRelationPredicateBucket filter, IGroupByCollection groupByClause, Boolean allowDuplicates) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.10\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 391
            at OurCode.GetNumberRows() 
         Inner exception: System.ArgumentException
            Message: Parameter value '1234' is out of range.
            Source: System.Data
            TargetSite: System.Threading.Tasks.Task TdsExecuteRPC(System.Data.SqlClient.SqlCommand, System.Data.SqlClient._SqlRPC[], Int32, Boolean, System.Data.Sql.SqlNotificationRequest, System.Data.SqlClient.TdsParserStateObject, Boolean, Boolean, System.Threading.Tasks.TaskCompletionSource`1[System.Object], Int32, Int32)
            ParamName: 
            StackTrace: 
               at System.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)
               at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
               at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
               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>b__13_0() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.10\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 137
               at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.TagAndExecuteCommand[TReturn](Func`1 toExecute) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.10\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 360
               at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.ExecuteScalar() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.10\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 137

The exception appears to be due to the parameter being created with exactly the data type of the column. The data type is numeric(3, 0) and the value 1234 is too big for that.

What would be the best way to approach this kind of problem?

The obvious solution would be to change the BuildEqualsPredicate function to test the value against the allowed range of the column. However that gets ugly very quickly. As you can imagine, there are lots of places where such a test would have to be included (there is more to the code than the above function), and there are lots of types that I would have to cover in this piece of code. Therefore I was hoping that there is a generic way to deal with this.

Also what I don't understand is that we don't have this problem with string values. I also remember this discussion from a few years ago, but it concerned Linq-to-LLBLGen and not RelationPredicateBucket: https://www.llblgen.com/tinyforum/Thread/24393/1; there string parameters were simply cut, maybe that's what happens here as well. If so I would find that inconsistent.

Thanks,

andreas

You'd either use the Precision and the MaxLength of the field (based on its type) to validate the parameter value's length (that would be the obvious solution).

Or just examine the exception and swallow it in that case, and either return a smart message to the user to refine his input, or just return zero results.

Regarding why there's no truncation... if you look at the FieldCompareValuePredicate class, line 283 and further, you'll see it will check for trunaction in the case of strings only. For numbers it simply will let the DB handle it. The truncation of strings has to be checked up front because we set the length of the parameter that will contain the value to the length of the field. So if the value is longer, it'll be truncated to the length of the field and potentially match even tho it wouldn't. We don't truncate numeric values.

Thank you for your replies.

What Walaa suggests is what I meant by "obvious" solution, which we were trying to avoid, because it is kind of ugly.

Since it works for strings, we thought that there was maybe a way to get this to work for other kinds of parameters.

But what you are saying is, is that there is no workaround for other kinds of parameters.

I still find it somewhat inconsistent, though.

We will have to go for the ugly solution then

The inconsistency comes from the fact that for e.g. numeric values like int, we don't set the size property of the parameter, so there's no truncation being performed by the parameter itself. We do that for strings as that's the way it is meant to be used. There's another difference: for numeric values there are implicit conversions possible, e.g. a value of 1 (int32) fits nicely in a byte, so should we not allow it or should be allow it? As it comes down to either us throwing an exception or the database, we let the database do it as it also knows best about its own implicit conversions (which might differ from database to database).