I am trying to run a query which will be downloaded into an excel report that contains 3017 records. This query contains a prefetch to get associated user data with it and I get the following exception:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled by user code Message="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." Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20"

The inital query seems to execute fine, but the prefetch query contains 3017 parameters so I did not want to post it.

Is there a way to run large queries with prefetching?

Do you set the ParameterisedPrefetchPathThreshold parameter to a high value? (On DaoBase / DataAccessAdapter) ?

Yeah, I tried at 4000. Will try it at higher levels.

Edit: I tried at 5, 10000 and 1,000,000 and I get the same query.

Edit:

            
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
      adapter.ParameterisedPrefetchPathThreshold = 1000000;
      adapter.FetchEntityCollection(transactionCollection, predicateBucket, 0, sortExpression, transactionPrefetch, pageNumber, pageSize);
}

since this is a report pageNumber = 0

You've to set it to a LOWER number, say 1000. Then, when there are > 1000 root entities, it will switch to a subquery instead of an IN(...) query, thus you avoid the exception.

I tried it with 1000 and I still the get exception with the IN query. Here is my prefetch:

EntityCollection transactionCollection = new EntityCollection(new PointTransactionEntityFactory());

IPrefetchPath2 transactionPrefetch = new PrefetchPath2((int)EntityType.PointTransactionEntity);
transactionPrefetch.Add(PointTransactionEntity.PrefetchPathAwardee);
transactionPrefetch.Add(PointTransactionEntity.PrefetchPathEventTransaction).SubPath.Add(EventTransactionEntity.PrefetchPathEvent);
transactionPrefetch.Add(PointTransactionEntity.PrefetchPathProgram);

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
      adapter.ParameterisedPrefetchPathThreshold = 1000;
      adapter.FetchEntityCollection(transactionCollection, predicateBucket, 0, sortExpression, transactionPrefetch, pageNumber, pageSize);
}

The resulting Transaction and Awardee queries (sanitized somewhat):

SELECT DISTINCT [PointTransaction].[TransactionID] AS [TransactionId], [PointTransaction].[AwardeeID] AS [AwardeeId] FROM ( [Awardee]  INNER JOIN [PointTransaction]  ON  [Awardee].[AwardeeID]=[PointTransaction].[AwardeeID]) WHERE ( ( [PointTransaction].[Type] = @Type1 AND [Awardee].[CustomerID] = @CustomerId2)) ORDER BY [PointTransaction].[TransactionTime] DESC


SELECT [Awardee].[AwardeeID] AS [AwardeeId], FROM [Awardee]  WHERE ( ( [Awardee].[AwardeeID] IN (@AwardeeId1, @AwardeeId2, .....))

If it helps, I am using the 1.0.2005.1 Final version.

Thanks, Joe

Do you use a field compare range predicate? What are the predicates you pass to the fetch?

No, just a normal RelationPredicateBucket.

IRelationPredicateBucket predicateBucket = new RelationPredicateBucket();
predicateBucket.PredicateExpression.Add((PointTransactionFields.Type == transactionType));
predicateBucket.PredicateExpression.Add(PredicateFactory.Between(PointTransactionFieldIndex.TransactionTime, startDate, endDate));
predicateBucket.Relations.Add(PointTransactionEntity.Relations.AwardeeEntityUsingAwardeeId);
predicateBucket.PredicateExpression.Add((AwardeeFields.CustomerId == customerId));

Could you just try to not set the threshold setting (it's default is 50).

I did not set it initially. It was only after the issue came up that I started setting the threshold value.

Would you try to run the "SELECT [Awardee].[AwardeeID] AS [AwardeeId], FROM [Awardee] WHERE ( ( [Awardee].[AwardeeID] IN (@AwardeeId1, @AwardeeId2, .....))" query directly in the database?

If you don't set the threshold and leave it to the deafualt 50, how many variables are there in the IN clause?

I could, but I would have to set 3017 parameters in the SQL.

From what I see happening, the first query executes and returns 3017 Transaction records. The second query then executes and uses the AwardeeId in each of the Transaction records to fetch the awardee data.

No matter what value I set in the threshold, I still get the query with 3017 parameters. I have experimented with different values for the threshold, both high and low, and get the same results.

Should I try to upgrade to version 2.0? We have it and another project uses it, but this particular project is still on the 1.0.2005.1 Final (July 6th, 2006) version.

I'm willing to try pretty much anything.

Thanks for the support.

The code checks the # of returned rows. If it's > the threshold (default: 50), it will use subquery prefetch paths, otherwise an IN() query. As 3000+ is way above 50, it should opt for the subquery prefetch path routine. Very strange that it opts for the IN() query variant. You use the vanilla runtimes, or do you use a custom build runtime lib ?

This code should work in 1.0.2005.1.

We use the plain runtimes. No custom builds or anything like that. The only "custom" thing we have is a template that creates an extra partial entity code file where we put our own properties.

Hmm, and no overrides to FetchEntityCollection in a derived class of DataAccessAdapter?

Nope, we are not overriding anything in the DBSpecific layer or in the DBGeneric layer.

I can't reproduce it,

with a threshold of 50 (default), I'm fetching 91 customer entities and all their order entities. This is resulting in a subquery using query to fetch the orders, not an IN query.

When I set the threshold to 100, I'll get the IN query.

So, I'd like you to do the following for me. Download the latest Runtime library sourcecode from the customer area, for 1.0.2005.1. In the sourcecode, you should have a file in the ORMSupportclasses folder called Make20_debug.cmd

Open AssemblyInfoDotNet20.cs in the ORMSupportclasses in an editor and comment out the signing key filename. Save the file and on a command prompt, first do: vsvars32 This loads the vs.net 2005 paths into the Path environment variable. then do: make20_debug.cmd This builds the ormsupportclasses dll in debug mode.

Go to SqlServerDQE folder on the command prompt, and there, also open AssemblyInfoDotNet20.cs and comment out the signing key path.

Then, run in the SqlServerDQE folder the file make20_debug.cmd. The SqlServer DQE is now build in debug mode. This is necessary as you're using an unsigned ormsupportclasses dll now and the DQE has to reference that one instead.

In your own application, reference the ORMSupportClasses dll in the folder: ORMSupportClasses\DotNET2.0\bin and the SqlServer DQE from SqlServerDQE\DotNET2.0\bin

You can now break in the ormsupportclasses code.

When you've your solution loaded into VS.NET 2005, open the file DataAccessAdapterBase.cs from the ORMSupportClasses folder. In there you'll find a method called FetchPrefetchPath.

Place a breakpoint at this line: if((((setCount * pkCount) <= ParameterisedPrefetchPathThreshold) && (pkCount>0)) || ((pkCount==0) && (setCount<=ParameterisedPrefetchPathThreshold)))

(in my code base it's line 4449)

Now run your code which crashes with the large parameters. You'll hit the breakpoint. It should decide to continue in the routine, instead of calling into FetchParameterisedPrefetchPath.

Could you check that for me please? If you need more help with this, let me know.

Ok, I was experminting with the prefetches, I have three with one subpath. I tried taking them down to one and running it.

IPrefetchPath2 transactionPrefetch = new PrefetchPath2((int)EntityType.PointTransactionEntity);
            //transactionPrefetch.Add(PointTransactionEntity.PrefetchPathAwardee);
            transactionPrefetch.Add(PointTransactionEntity.PrefetchPathEventTransaction); //.SubPath.Add(EventTransactionEntity.PrefetchPathEvent);
            //transactionPrefetch.Add(PointTransactionEntity.PrefetchPathProgram);

Come to find out the initial prefetch (fetching awardee) is not causing the problem. The problem is being caused second one. However, I have noticed that even with the first, I am still getting the same query type. It is still creating an IN clause.

I also realized that I did not include the stack trace in the exception detail in the first post and that may help in determining whats going on. The complete exception is:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled by user code
  Message="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."
  Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20"
  StackTrace:
       at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\RuntimeSource\ORMSupportClasses\RetrievalQuery.cs:line 92
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IValidator validatorToUse, IEntityFields2 fieldsUsedForQuery) in c:\RuntimeSource\ORMSupportClasses\DataAccessAdapterBase.cs:line 597
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Int32 pageNumber, Int32 pageSize) in c:\RuntimeSource\ORMSupportClasses\DataAccessAdapterBase.cs:line 3995
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Int32 pageNumber, Int32 pageSize) in c:\RuntimeSource\ORMSupportClasses\DataAccessAdapterBase.cs:line 2280
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchParameterisedPrefetchPath(IEntityCollection2 rootEntities, Int64 maxNumberOfItemsToReturn, IPrefetchPath2 prefetchPath) in c:\RuntimeSource\ORMSupportClasses\DataAccessAdapterBase.cs:line 4862
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Int32 pageNumber, Int32 pageSize) in c:\RuntimeSource\ORMSupportClasses\DataAccessAdapterBase.cs:line 2323
       at Pointfolio.BusinessLayer.ReportSources.TransactionReportSource.FetchReport(Guid awardeeId, DateTime startDate, DateTime endDate, PointTransactionType transactionType, Int32 pageNumber, Int32 pageSize) in C:\Documents and Settings\jyoung\My Documents\Visual Studio 2005\Projects\Pointfolio_v2\BusinessLayer\ReportSources\TransactionReportSource.cs:line 85
       at Pointfolio.BusinessLayer.ReportSources.TransactionReportSource.FetchReport(Guid awardeeId, DateTime startDate, DateTime endDate, PointTransactionType transactionType) in C:\Documents and Settings\jyoung\My Documents\Visual Studio 2005\Projects\Pointfolio_v2\BusinessLayer\ReportSources\TransactionReportSource.cs:line 52
       at Forms_Reports_Transactions.ExportReport() in c:\Documents and Settings\jyoung\My Documents\Visual Studio 2005\WebSites\Pointfolio_v2\Forms\Reports\Transactions.aspx.cs:line 139
       at Forms_Reports_Transactions.btnDownloadReport_Click(Object sender, EventArgs e) in c:\Documents and Settings\jyoung\My Documents\Visual Studio 2005\WebSites\Pointfolio_v2\Forms\Reports\Transactions.aspx.cs:line 65
       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Frans, I got it running the debug libs like you asked, but the break point does not get hit when the exception occurs.

Edit: I am currently trying to reproduce it in a test environment. I'll let you know how it goes.

Ok, I can reproduce the query in a test environment.

I used a normal Customer / Order Schema where a customer has multiple Orders. Each Order has multiple OrderItems and each OrderItem has one Product.

_Edit: The idea was to fetch all the customers who order a particular product. I may be completly abusing the framework like this. _

Here is the code I used. Its not pretty

        private void button1_Click(object sender, EventArgs e)
        {
            Guid productId = Guid.NewGuid();

            CreateTestData(productId);

            IRelationPredicateBucket predicateBucket = new RelationPredicateBucket();
            predicateBucket.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId);
            predicateBucket.Relations.Add(OrderEntity.Relations.OrderItemEntityUsingOrderId);
            predicateBucket.Relations.Add(OrderItemEntity.Relations.ProductEntityUsingProductId);

            predicateBucket.PredicateExpression.Add(OrderItemFields.ProductId == productId);

            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CustomerEntity);
            prefetchPath.Add(CustomerEntity.PrefetchPathOrders);

            ISortExpression sortExpression = new SortExpression(OrderFields.DatePlaced | SortOperator.Ascending);

            EntityCollection customerCollection = new EntityCollection(new CustomerEntityFactory());

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.ParameterisedPrefetchPathThreshold = 500;
                adapter.FetchEntityCollection(customerCollection, predicateBucket, 0, sortExpression, prefetchPath, 0, 20);
            }

            DeleteTestData();
        }

        private void CreateTestData(Guid productId)
        {
            ProductEntity product = new ProductEntity(productId);
            product.Name = "Test Product";
            product.Description = "This is a test";
            product.Price = 1.57m;

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.SaveEntity(product, true);
            }

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                for (int i = 1; i < 1000; i++)
                {
                    // create a customer with some order and order items
                    CustomerEntity customer = new CustomerEntity(Guid.NewGuid());
                    customer.FirstName = "FirstName" + i.ToString();
                    customer.LastName = "LastName" + i.ToString();

                    OrderEntity order = new OrderEntity(Guid.NewGuid());
                    OrderItemEntity orderItem = new OrderItemEntity(Guid.NewGuid());
                    orderItem.ProductId = productId;
                    orderItem.Quantity = 2;

                    order.OrderItems.Add(orderItem);

                    customer.Orders.Add(order);

                    adapter.SaveEntity(customer);
                }
            }
        }

        private void DeleteTestData()
        {
            CustomerEntity customer = new CustomerEntity();
            ProductEntity product = new ProductEntity();

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                // should cascade delete the orders
                adapter.DeleteEntitiesDirectly(customer.LLBLGenProEntityName, null);

                adapter.DeleteEntitiesDirectly(product.LLBLGenProEntityName, null);
            }
        }

It would have been better to use test Setup and Teardowns but oh well.

It won't throw the exception but, it will generate the query with an IN clause instead of a subclause.

Thanks! I'll see if I can repro it first thing in the morning.

I see you're specifying paging. Paging works with parameterized prefetch paths, thus with the IN query approach. However, it should work, as the limit of the entities to fetch is as big as the page size, thus shouldn't be a problem

When I run my test with customers and orders and fetch a page of 20 entities, I also get 100 entities in the query when I set the threshold to 100... Really odd. I'll check it out. It's always good to know your own code . ( I passed 0, like you, for pageNumber)

Ok, for pageNumber, you've to start counting with 1, not 0. When you specify 0, no paging takes place, and all entities are read. As Paging uses the IN() approach when you've specify a prefetch path (it's the only way), you'll end up with a lot of parameters in the query.

So specify 1 instead of 0 for pageNumber, and it should work.

Ok I am a bit confused now.

I was trying to prevent paging by suppling a 0 in the pageNumber parameter on FetchEntityCollection.

If I understand you right, I need to supply a 1 as the pageNumber.

I read in the code comments that for pageNumber if 0 no paging logic is applied and for pageSize if smaller than 2 no paging logic is applied, which seems to contradict setting the pageNumber to 1.

After a few expriements I found that

adapter.FetchEntityCollection(customerCollection, predicateBucket, 0, sortExpression, prefetchPath);

and

adapter.FetchEntityCollection(customerCollection, predicateBucket, 0, sortExpression, prefetchPath, 1, 1);

produce the same type of query.

So after all that it comes down to a very simple fix on my part.

On a side note. I have been using LLBLGen for about a year now. We finally have our solution in production and after the initial learning curve of how to use LLBLGen I can't imagine developing data driven apps without it. You have a really sweet product here and the fact that I can chat with you when questions arise is just insanely cool.

jyoung wrote:

Ok I am a bit confused now.

I was trying to prevent paging by suppling a 0 in the pageNumber parameter on FetchEntityCollection.

If I understand you right, I need to supply a 1 as the pageNumber.

IF you want paging If you don't want paging, call the other overload which doesn't accept paging parameters.

In v2.0 a call like: adapter.FetchEntityCollection(customers, null, 0, null, path, 0, 20); will automatically call adapter.FetchEntityCollection(customers, null, 0, null, path);

because one of the paging parameters is 0. In 1.0.2005.1, this isn't the case. There you've to call the overload which doesn't accept paging parameters.

I read in the code comments that for pageNumber if 0 no paging logic is applied and for pageSize if smaller than 2 no paging logic is applied, which seems to contradict setting the pageNumber to 1.

It indeed should have been '1' instead of 2. So if pageNumber or pageSize is 0 or smaller, paging is disabled, and all rows are returned (or to the limit you set with maxNumberOfItemsToReturn). I'll fix that in the 2.0 refmanual, it's there too.

After a few expriements I found that

adapter.FetchEntityCollection(customerCollection, predicateBucket, 0, sortExpression, prefetchPath);

and

adapter.FetchEntityCollection(customerCollection, predicateBucket, 0, sortExpression, prefetchPath, 1, 1);

produce the same type of query.

Well, the second one returns 1 entity, the first one could return more. (but depends on your filter )

So after all that it comes down to a very simple fix on my part.

On a side note. I have been using LLBLGen for about a year now. We finally have our solution in production and after the initial learning curve of how to use LLBLGen I can't imagine developing data driven apps without it. You have a really sweet product here and the fact that I can chat with you when questions arise is just insanely cool.

Thanks! .

While this worked fine in my test:

adapter.FetchEntityCollection(customerCollection, predicateBucket, 0, sortExpression, prefetchPath, 1, 1);

it does not work so well the in the production code base. It executes a TOP(1) query.

adapter.FetchEntityCollection(transactionCollection, predicateBucket, 0, sortExpression, transactionPrefetch, pageNumber, pageSize);

where pageNumber = 1 and pageSize = 1 results in:

SELECT DISTINCT TOP 1 [PointTransaction].[TransactionID] AS [TransactionId], ...

What do I use for the pageSize?

If I don't supply any paging info:

adapter.FetchEntityCollection(transactionCollection, predicateBucket, 0, sortExpression, transactionPrefetch);

It works fine. I can accept this by checking if pageNumber and pageSize are set to 0 then use the none paging FetchEntityCollection instead of the paging version.

Frans, I think you posted your response right before mine.

After reading yours you pretty much explained what I was seeing and now things make sense.

Things are working good now.

Thanks again for all the help.

I will go ahead and close this thread.