Too many parameters were provided in this RPC request, max is 2100

Hi,

I'm trying to get DevExpress LinqInstantFeedBackSource component to work with LS. As long as I don't use grouping in my grid, everything is fine. From the moment I group (a lot of data), I get the following error:

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.

Can somebody please help me out. I don't have this problem when I use Entity Framework.

Greetings, Sigurd

Are you able to capture and post the SQL being generated? This sounds like a very large IN() clause has been generated. If you are not comfortable posting the SQL directly if you can just confirm the nature of what the query is doing.

You can set this as per the documentation here: http://www.mindscapehq.com/documentation/lightspeed/Testing-and-Debugging/Logging and then the SQL will be emitted to any TraceListeners (e.g. if this is an ASP.NET site, you can view this tracing information by enabling tracing http://msdn.microsoft.com/en-us/library/wwh16c6c.aspx) if you are using the TraceLogger, or alternatively just implement a custom logger if its more appropriate for you to be sending the output somewhere custom.

Jeremy

Hi,

First of all, thanks for the quick response.

I'm almost sure it generates a very long IN() clause. But I don't see the reason why this is done this way. EF has no problem with this for some reason and XPO also not. I guess it has to do with how the LINQ query is translated to SQL.

Here you see an example how this can be solved: https://connect.microsoft.com/VisualStudio/feedback/details/475984/use-an-xml-type-parameter-in-order-to-exceed-the-2100-parameter-limit-in-linq-to-sql-when-filtering-via-a-local-collection.

Greetings, Sigurd

We use the IN() clause to fetch the results on a per group basis after the initial GROUP BY query has returned. I couldn't really comment much on EF or XPO other than they will undoubtedly doing things differently.

Thanks for suggesting a solution but this is not appropriate for LightSpeed as we need an approach which is portable across the database providers we support and can be expressed using our Query API.

For now unfortunately there is no real workaround to this if you are going through the LINQ provider since this is just how it works but I will pop an item on our backlog to investigate perhaps splitting the queries when we approach the data provider parameter limit.

Jeremy

Hi,

I was afraid of this. This is not good news, but understandable for the moment.

While adding the logger I found another problem when grouping decimals. In Belgium (and other countries) the decimal point is a comma. If you take a look at the following query, you'll understand the problem IN().

The error I get not a nice one :(.

Object of type 'Mindscape.LightSpeed.Linq.Grouping2[System.Decimal,LightSpeed01.DocumentRetributieEditor]' cannot be converted to type 'System.Linq.IGrouping2[System.Decimal,VB$AnonymousType_020[System.Guid,System.String,System.String, System.Nullable1[System.DateTime],System.Decimal,System.String,System.Byte,System.String,System.String, System.Decimal,System.Decimal,System.String,System.String,System.Guid,System.Boolean, System.Boolean,System.Byte,System.Nullable`1[System.Decimal],System.String,System.String]]'.

The SQL query...

SELECT DocumentRetributieEditor.DCMTID AS [DocumentRetributieEditor.DCMTID], DocumentRetributieEditor.CONTKenmerk AS [DocumentRetributieEditor.CONTKenmerk], DocumentRetributieEditor.DCAMKost AS [DocumentRetributieEditor.DCAMKost], DocumentRetributieEditor.DCBTBedrag AS [DocumentRetributieEditor.DCBTBedrag], DocumentRetributieEditor.DCMTCONTID AS [DocumentRetributieEditor.DCMTCONTID], DocumentRetributieEditor.DCMTCreDat AS [DocumentRetributieEditor.DCMTCreDat], DocumentRetributieEditor.DCMTDatumUitvoer AS [DocumentRetributieEditor.DCMTDatumUitvoer], DocumentRetributieEditor.DCMTFacGemeente AS [DocumentRetributieEditor.DCMTFacGemeente], DocumentRetributieEditor.DCMTFacStraat AS [DocumentRetributieEditor.DCMTFacStraat], DocumentRetributieEditor.DCMTInfo AS [DocumentRetributieEditor.DCMTInfo], DocumentRetributieEditor.DCMTIsAfgewerkt AS [DocumentRetributieEditor.DCMTIsAfgewerkt], DocumentRetributieEditor.DCMTIsBetaald AS [DocumentRetributieEditor.DCMTIsBetaald], DocumentRetributieEditor.DCMTLevGemeente AS [DocumentRetributieEditor.DCMTLevGemeente], DocumentRetributieEditor.DCMTLevPostcode AS [DocumentRetributieEditor.DCMTLevPostcode], DocumentRetributieEditor.DCMTLevStraat AS [DocumentRetributieEditor.DCMTLevStraat], DocumentRetributieEditor.DCMTLevStraatBus AS [DocumentRetributieEditor.DCMTLevStraatBus], DocumentRetributieEditor.DCMTLevStraatNr AS [DocumentRetributieEditor.DCMTLevStraatNr], DocumentRetributieEditor.DCMTLeverAdres AS [DocumentRetributieEditor.DCMTLeverAdres], DocumentRetributieEditor.DCMTReferentie AS [DocumentRetributieEditor.DCMTReferentie], DocumentRetributieEditor.DCMTSCDTID AS [DocumentRetributieEditor.DCMTSCDTID], DocumentRetributieEditor.DCMTStatus AS [DocumentRetributieEditor.DCMTStatus], DocumentRetributieEditor.DCMTTotBedrIncl AS [DocumentRetributieEditor.DCMTTotBedrIncl], DocumentRetributieEditor.DCMTType AS [DocumentRetributieEditor.DCMTType], DocumentRetributieEditor.DCMTVRKPID AS [DocumentRetributieEditor.DCMTVRKPID], DocumentRetributieEditor.DCMTVerkoperID AS [DocumentRetributieEditor.DCMTVerkoperID], DocumentRetributieEditor.DCRBARTLID AS [DocumentRetributieEditor.DCRBARTLID], DocumentRetributieEditor.DCRBLANDID AS [DocumentRetributieEditor.DCRBLANDID], DocumentRetributieEditor.DCRBNrPlaat AS [DocumentRetributieEditor.DCRBNrPlaat], DocumentRetributieEditor.DCRBOvertredingTYPEID AS [DocumentRetributieEditor.DCRBOvertredingTYPEID], DocumentRetributieEditor.DCRBTerHoogteVan AS [DocumentRetributieEditor.DCRBTerHoogteVan], DocumentRetributieEditor.LastDCBTDatum AS [DocumentRetributieEditor.LastDCBTDatum], DocumentRetributieEditor.Saldo AS [DocumentRetributieEditor.Saldo] FROM DocumentRetributieEditor WHERE (DocumentRetributieEditor.DCMTID = DocumentRetributieEditor.DCMTID AND DocumentRetributieEditor.DCMTTotBedrIncl IN (0,0000, 6,0000, 8,0000, 9,1000, 10,0000, 12,0000, 14,0000, 15,0000, 16,0000, 17,0000, 18,0000, 18,8000, 20,0000, 22,0000, 24,0000, 26,0000, 27,6000, 30,0000, 30,6000, 31,0000, 32,0000, 33,7000, 34,0000, 36,0000, 60,0000, 61,8000, 63,0000, 72,0000, 112,0000, 124,0000)) ORDER BY DocumentRetributieEditor.DCMT_TotBedrIncl

Greetings, Sigurd

Hi,

Additional info...

This error seems to happen when I select a subset of the fields in my LINQ query. When I request the complete entity, I don't have this error. How come I can't ask for a subset of my views fields?

The comma in numbers remains though.

Greetings, Sigurd

Thanks - I will have a look into the comma's issue. I dont quite understand what you are meaning by:

When I request the complete entity, I don't have this error. How come I can't ask for a subset of my views fields?

You can select a subset of the fields/properties by using a projection - e.g. linqQuery.Select(e => new { e.Property1, e.Property2 }) or by using UnitOfWork.Project. What is the error you are referring to?

Sorry actually just re-reading your message about the comma's, you are only seeing this in the logger output (correct?) not actually in the literal statement which is going down to the server (which is parameterised). So you are not seeing a message about an incorrectly structured statement? If so you are seeing a non issue as the logger output expands the parameter values for readability and doesn't necessarily reflect the exact text sent to the server.

Im therefore assuming that the stack trace refers to the other issue you were seeing? If so can you please send through a minimal repro project which exhibits this behavior (usually a console app or unit test class) and I can have a look into this for you.

Jeremy

BONUS TIP: If you set LightSpeedContext.VerboseLogging to true, the logger will print out the individual command parameters and their values. This can make it easier to understand what the actual query was (as opposed to the prettyprinted version).

Hi,

It's possible the comma's are only in the output. It seems to work fine, but looks weired when debugging.

Actually, it's quite difficult to give this project, because you'll need devexpress (LinqInstantFeedBackSource) installed on your system to see it. Next to that, you'll need our database which is several GBs in size.

The main idea behind this all is the following. The DevExpress GridControl allows the developer to use the LinqInstantFeedBackSource component to only fetch the visible rows (and some above and below) in a seperate thread to keep the UI thread free. When a user scrolls, changes the order by or groups by a column, the LinqInstantFeedBackSource component asks for a base Linq query which it uses to further filter and request the data for the visible rows. The first code below works fine, but fetches too much columns for this particular grid.

Dim varList = From c In uow.DocumentRetributie_Editors Select c

The following line works fine as long as the user doesn't use grouping. The error comes when the user tries to group a column. I think LS needs more info than just these columns, but since I'm too new with LS, I can only guess.

Dim varList = From c In uow.DocumentRetributie_Editors Select c.Id, c.CONT_Kenmerk, c.DCMT_Referentie, c.DCMT_Status

PS: VerboseLogging turned on now, thx for the tip.

Greetings, Sigurd

Hi,

Ok, I got a lot further... The following line of code seems to work for this particular part. It is not the way I expected LS to work, but there seems to be a problem when converting anonymous type back to LS entity. I don't know if there is a speed penalty involved by this, but I guess there is.

Dim varList = From c In uow.DocumentRetributie_Editors Select New LightSpeed01.DocumentRetributie_Editor With {.Id = c.Id, .CONT_Kenmerk = c.CONT_Kenmerk, .DCMT_Referentie = c.DCMT_Referentie, .DCMT_Status = c.DCMT_Status}

With this 'workable', I still have the 2100 maximum problem, which can't be solved without you changing your code :).

Greetings, Sigurd

Ive tried to reproduce the exception you reported but have not been successful. Unfortunately we cant really do much more here without a repro case.

With your most recent comment (problem when converting anonymous type back to LS entity), this is not what you are doing - you are asking for the result to project into a new instance of your entity type. While this works (although you cannot set the Id property, so that wont work) you should never do this. We expect an entity to be loaded in its entirety so if you load a partial entity and then make a modification and save your changes then we will save down the default values on the unassigned properties. Rather you should be selecting out an anonymous type instance containing the partial properties you want to use. If you want to select out an entity instance, just select the entity directly. If you are concerned about spurious columns being selected out (e.g. if you have some large data columns which you dont always want selected out) then look at using named aggregates to only load those particular columns when appropriate. (see: http://www.mindscapehq.com/documentation/lightspeed/Performance-and-Tuning/Controlling-How-Entity-Data-Loads)

Jeremy