After I replied in the recent "Efficient multiple inserts - SQL statement INSERT INTO .. SELECT", I had an immediate need to do something similar but likely more of a bacth UPDATE rather than INSERT.

I tried to use the built-in UpdateEntitiesDirectly but I have a problem with this: I have a list of 6,000 or so items I want to update but LLBLGen insists on using Parameters and this exceeds to the 2100 limit for SQL Server.

To get around this, I used a Linq method to batch the 6,000 to into 1,000 item sets:-

            commissionItemTemplate.PayrollPaymentID = payrollPaymentID;

            // Assign to commissionItems
            foreach (var commissionBatch in commissionIDsCovered.Batch(MaxBatchSize, ints => ints.ToArray()))
            {
                adapter.UpdateEntitiesDirectly(commissionItemTemplate, new RelationPredicateBucket(CommissionItemFields.ID == commissionBatch));
            }

This all works fine but I have now hit the Parameters limit again whilst fetching a set of entities for which I have the IDs (6,000 again) stored locally.

1) Is there any way to persuade LLBLGen to not use parameters for a given IN clause but write the numbers directly? 2) Is there any way of telling a query to automatically bring back the results xxx rows at a time internally. (I can only see the .Page() construct but I really don't want to be doing this manually)

I suspect that for (1) I should be writing a custom FieldCompareRangePredicate but is rewriting ToQueryText enough??

LLBLGen v4.0 Final (July 18th, 2013)

1) Is there any way to persuade LLBLGen to not use parameters for a given IN clause but write the numbers directly?

You don't want to do that, no parameters opens the door for SQL injection, and defies the entire concept.

2) Is there any way of telling a query to automatically bring back the results xxx rows at a time internally. (I can only see the .Page() construct but I really don't want to be doing this manually)

Not sure what you need here, are you talking about returning the TOP xxx records?

You don't want to do that, no parameters opens the door for SQL injection, and defies the entire concept. Not sure what you need here, are you talking about returning the TOP xxx records?

I don't think SQL injection is possible in this scenario since the output is numbers, not strings and under control of the application. (Interested to learn if that isn't correct though).

I have a query. It is fairly large so I won't post it here unless you think it would help. Its WHERE clause is something like this:-

.Where(CommissionItemFields.ID == commissionStatementIDs)

thus creating a FieldCompareRangePredicate.

commissionStatementIDs is an int[] that gets passed to the method creating/running the query. Sometimes the array is small and sometimes it has a length of 6000. If it happens to be >2100 then the method will crash because SQL Server only supports that number of parameters as a maximum.

If the query was written to include "IN (1, 2, 3, ..., 6000) rather than use 6000 parameters then it works since the 2100 limit doesn't apply.

After the query is created, I would ideally like to keep the same, simple fetch code but with a parameter telling LLBLGen to automatically do any paging necessary in xxx row chunks - say 2000.

        using (var adapter = new DataAccessAdapter())
        {
            return adapter.FetchQuery(query, 2000);
        }

I don't believe this is possible but I thought I'd ask in case I'd missed something.

Another workaround which would still avoid SQL Injection would be to use a temporary table but I don't believe LLBLGen can support that?

simmotech wrote:

You don't want to do that, no parameters opens the door for SQL injection, and defies the entire concept. Not sure what you need here, are you talking about returning the TOP xxx records?

I don't think SQL injection is possible in this scenario since the output is numbers, not strings and under control of the application. (Interested to learn if that isn't correct though).

that would indeed be hard to do, unless the numbers are stored as strings somewhere.

I have a query. It is fairly large so I won't post it here unless you think it would help. Its WHERE clause is something like this:-

.Where(CommissionItemFields.ID == commissionStatementIDs)

thus creating a FieldCompareRangePredicate.

commissionStatementIDs is an int[] that gets passed to the method creating/running the query. Sometimes the array is small and sometimes it has a length of 6000. If it happens to be >2100 then the method will crash because SQL Server only supports that number of parameters as a maximum.

If the query was written to include "IN (1, 2, 3, ..., 6000) rather than use 6000 parameters then it works since the 2100 limit doesn't apply.

You could create your own fieldcompare range predicate, which inlines the values instead of creating parameters and use that, that would solve it for you. There's no built-in way to do what you want, it requires a custom predicate (which is easy to create).

After the query is created, I would ideally like to keep the same, simple fetch code but with a parameter telling LLBLGen to automatically do any paging necessary in xxx row chunks - say 2000.

        using (var adapter = new DataAccessAdapter())
        {
            return adapter.FetchQuery(query, 2000);
        }

I don't believe this is possible but I thought I'd ask in case I'd missed something.

can't you use normal paging for that?

Another workaround which would still avoid SQL Injection would be to use a temporary table but I don't believe LLBLGen can support that?

Inserts in temp tables, no you have to formulate the query yourself then, as the temp table target is not settable using entities, you have to update the generated query to specify the temptable instead of the table generated into the query.

can't you use normal paging for that?

Maybe paging is a red herring since the query would crash anyway due to the filter. Thanks all.

Here is the code I have for the custom predicate in case its useful to anyone else:

    public class FieldCompareRangePredicateAce: FieldCompareRangePredicate
    {
        public FieldCompareRangePredicateAce(EntityField2 field, IEnumerable values): base(field, null, new object[] { values })
        {}

        public override string ToQueryText(bool inHavingClause)
        {
            if (FieldCore == null) return "";
            if (Values.Count <= 0) return Negate ? "1=1" : "1=0";

            if (DatabaseSpecificCreator == null) throw new ApplicationException("DatabaseSpecificCreator object not set. Cannot create query part.");

            Parameters.Clear();

            var stringBuilder = new StringBuilder(128);
            stringBuilder.Append(DatabaseSpecificCreator.CreateFieldName(FieldCore, PersistenceInfo, FieldCore.Name, ObjectAlias, inHavingClause));
            stringBuilder.Append(" ");

            if (FieldCore.ExpressionToApply != null)
            {
                for (var i = 0; i < FieldCore.ExpressionToApply.Parameters.Count; ++i)
                {
                    Parameters.Add(FieldCore.ExpressionToApply.Parameters[i]);
                }
            }

            if (Negate) stringBuilder.Append("NOT ");

            stringBuilder.Append("IN (");

            for (var i = 0; i < Values.Count; ++i)
            {
                if (i > 0) stringBuilder.Append(", ");

                stringBuilder.Append(Values[i]);
            }

            stringBuilder.Append(")");

            return stringBuilder.ToString();
        }
    }

<<<< Quote:

I have a query. It is fairly large so I won't post it here unless you think it would help. Its WHERE clause is something like this:-

Code: .Where(CommissionItemFields.ID == commissionStatementIDs)

thus creating a FieldCompareRangePredicate.

commissionStatementIDs is an int[] that gets passed to the method creating/running the query. Sometimes the array is small and sometimes it has a length of 6000. If it happens to be >2100 then the method will crash because SQL Server only supports that number of parameters as a maximum.

If the query was written to include "IN (1, 2, 3, ..., 6000) rather than use 6000 parameters then it works since the 2100 limit doesn't apply.

You could create your own fieldcompare range predicate, which inlines the values instead of creating parameters and use that, that would solve it for you. There's no built-in way to do what you want, it requires a custom predicate (which is easy to create).

I followed the suggested solution above and used a custom predicate for large in lists. Yes it worked for SQL Server in my example with list containing 6000 items, but the same failed for Oracle (complaining the list is longer than 1000). Any suggestions please?

Thanks

Could you please troubleshoot and capture the generated SQL command and post it over here?

In the future please create new threads instead of using old ones, as per the forums guideline.

Oracle has a hard limit on the number of elements in an IN clause which is 1000. Please check where the numbers come from, and if possible retrieve them from the DB in a subquery instead of an IN clause with 6000 values as that's a lot.

I.e. if the 6000 values come from a table so the 6000 values are obtainable through a select on that table, use that instead.