The runtime framework (3.0) gives me the 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." (using SQL Server 2008 ).

This is because a "SELECT ... WHERE Id IN {more than 2100 values}" query is used in the following code:

var entities = new EntityCollection<ProductsEntity>();
SortExpression sorter = null;
using(var adap = new DataAccessAdapter())
{
    sorter = new SortExpression(new SortClause(ProductsFields.Id, null, SortOperator.Ascending));

    var prefetchPath = new PrefetchPath2(EntityType.ProductsEntity);
    prefetchPath.Add(ProductsEntity.PrefetchPathBrands); // Product 1:m Brand
    // Exception occurs if I add this:
    prefetchPath.Add(ProductsEntity.PrefetchPathProductPrices); // Product 1:n ProductPrice

    adap.FetchEntityCollection(entities, new RelationPredicateBucket(), 0, sorter, prefetchPath, 1, 5000);
}

Why doesn't that use a SQL paging query ("LIMIT")? If I remove the line after ###, it works without a problem. Any solution or workaround?

There is a default threshold for PrefetchPaths that lets the framework revert to a subQuery, and I don't see you changing this threshold.

Which LLBLgen Pro runtime library version (build number) are you using?

SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll = v2.0.50727

What would be the sense in using "WHERE Id IN ..." instead of paging here? Or what is the use case for that threshold? How do I change it?

AndiDog wrote:

SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll = v2.0.50727

That is not the Runtime Library version. Check this to know how to know it.

AndiDog wrote:

What would be the sense in using "WHERE Id IN ..." instead of paging here? Or what is the use case for that threshold? How do I change it?

The threshold is used to indicate LLBLGen runtime whether to use SELECT ThePrefetchTable WHERE Id in (SUBQUERY) or SELECT ThePrefetchTable WHERE Id IN (@p1, @p2, ...).

This is explained in Optimizing Prefetch Paths. The thing is: by default the DataAccessAdapter.ParameterisedPrefetchPathThreshold is set to 50, so in your case where you are fetching more than 2100 entities, it should use the SubQuery approach. So our guess is that you modify the DataAccessAdapter.ParameterisedPrefetchPathThreshold property somewhere so the Parameter query approach is used. Of course this gives the errors since SQLServer only support 2100 parameters in this style. So, Did you modify the DataAccessAdapter.ParameterisedPrefetchPathThreshold somewhere?

I did not modify that threshold anywhere (it is still set to 50).

The "File version" shown for the DLL is "3.0.10.915".

Mm. I can't reproduce it.

Just in case please update to the latest runtime library version (you are using an old one). If that doesn't help please attach a reproducible solution which should include a script database, the llblgen project and the code that reproduce the behavior (without bins and assemblies, just code) all zipped. You can use the HelpDesk forum which is private.

Thanks. Where do I get the updated 3.0.* runtime DLLs? I tried the ones from LLBLGen 3.1 but they're not compatible.

You should have a link for v.3.0 downloads in the customers area.

You should upgrade to v3.1 if possible, as it's the latest version and bugfixes are made to that code base and only ported back to v3.0 if people ask for it.