Hi, We are using LLBLGenPro2.0 version in our web application.

In our application we have a method called "getPrimaryMembersRatio" to do some calculations.

Parameters are 1.entityid string[] 2.years string[]

It returns as "LLBLGen TypedView" object.

The method "getPrimaryMembersRatio" is working fine until entityid string[] elements <=2100.

When we pass the collection for entityid which are having more than 2100 values[elements],then we got the error "Maximum parameters 2100".

Our approaches were

First:

PredicateFactory.CompareValue(EntityAverageRatiosViewFieldIndex.EntityId , ComparisonOperator.Equal, entityId)

Second:

Then we got the suggestion from LLBLGen forum we updated like objFilterExp.Add(new FieldCompareSetPredicate(EntityAverageRatiosViewFields.EntityId, entityId, SetOperator.In, objPGFilterExp));

It was not working since the entityId is not entityfield. Also we don't want to go for creating temp tables to store the array collections.

Third:

Currently we are using for loop to achieve this like below:

public EntityAverageRatiosViewTypedView getPrimaryMembersRatio(string[] entityId, string[] years) { EntityAverageRatiosViewTypedView objPrimaryRatio = null; try { //Resetting the CommandTimeOut DbUtils.CommandTimeOut = CDMS.COMMON.StaticMembers.CommandTimeout;

            objPrimaryRatio = new EntityAverageRatiosViewTypedView();

         foreach (string entity_id in entityId)
            {
                IPredicateExpression objFilterExp = new PredicateExpression();

                objFilterExp.Add(EntityAverageRatiosViewFields.EntityId == entity_id);                  
                objFilterExp.AddWithAnd(EntityAverageRatiosViewFields.FlagPgmember == 1);
                objFilterExp.AddWithAnd(EntityAverageRatiosViewFields.ReportingYear == years);

                EntityAverageRatiosViewTypedView tempview = new EntityAverageRatiosViewTypedView();

                tempview.Fill(0, null, true, objFilterExp);

                objPrimaryRatio.Rows.Add(tempview.Rows[0].ItemArray);
            }

            return objPrimaryRatio;
        }
        catch (Exception ex)
        {
            CDMS.COMMON.ExceptionLog.Log.Error(ex.Message, ex);
            throw ex;
        }

    }

The third approach is getting too slow.

Can anyone suggest how can we achieve this with better performance?

Thanks in advance, Balaji

Hello,

Did you try to add the entityId using a fieldcomparerangepredicate without the for :

IPredicateExpression objFilterExp = new PredicateExpression();

                    objFilterExp.Add(EntityAverageRatiosViewFields.EntityId == entityId);                   
                    objFilterExp.AddWithAnd(EntityAverageRatiosViewFields.FlagPgmember == 1);
                    objFilterExp.AddWithAnd(EntityAverageRatiosViewFields.ReportingYear == years);

Hi, Thanks for your support..

We tried with the following one already..

IPredicateExpression objFilterExp = new PredicateExpression(); objFilterExp.Add(EntityAverageRatiosViewFields.EntityId == entityId); objFilterExp.AddWithAnd(EntityAverageRatiosViewFields.FlagPgmember == 1); objFilterExp.AddWithAnd(EntityAverageRatiosViewFields.ReportingYear == years);

Still we get the same error "Maximum parameter 2100."

Is there a possible solution to achieve this in LLBLGen? We extensively using LLBLGen..We need a quick approach for this..

Thanks in advance, Balaji

objFilterExp.Add(EntityAverageRatiosViewFields.EntityId == entityId);

The above will generate a a FieldCompareRangePredicate, which will be translated as:

WHERE EntityId IN (x, y, z, .....)

This is the right predicate but, most databases have limits to the number of marameters passed to the IN clause. Also your query will go slower as the number of parameters increase.

Best solution is to divide your range (array) into smaller chuncks (eg. 100 parameters each), and use multiple IN clauses OR-ed together, to give the following query.

WHERE EntityId IN (1, 2, 3, ...,100)
OR EntityId IN (101, 102, 103, ...,200)
OR ...

Hi, Thanks for your kind information.We will try to implement in that way what you suggested.

Balaji

It's a limitation of the database, and it's logical that this limitation exists. A complete query can't have more than 2100 parameters on sqlserver (on oracle its even lower, about 1000 I think).

So you have little choice in what to do. - create temptables with the values, IF These values aren't already in a table in the db.

OR

• if there are ranges in the values (e.g. 1-100), you could replace these numbers with a between predicate.

It's awkward, I know, but there's little we can do for you as the limitation is in the db.