locked
Entity Framework VS 2012 designer fails calling sp_executesql with too many parameters when updating large model from database RRS feed

  • Question

  • We recently upgraded to VS 2012 (still targeting .net 4.0) so we could use the new model diagrams to support having a larger merged data model.  We were able to merge our models by hand into one combined model and can use it without issue (402 tables with lots of associations).

    However when I try to update the model from the database, the designer gets an error:

    Error 3 An error occurred while executing the command definition. See the inner exception for details.
     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. 

    SQL Server (2008 r2) has a fixed limit of 2100 parameters that can be passed to a stored procedure.  EF is using a call to sp_executesql where it is passing 2400 parameters.  While debugging the exeception in visual studio, we saw may duplicates like using one parameter for each table for the catalog, the schema and the table name rather than reusing the catalog and schema parms (that alone would've kept the call under the limit).  Here's an example (notice each SchemaName has a different parm for a value that can't be different since you don't support mutliple databases in the same edmx!):

    WHERE (([Extent1].[CatalogName] LIKE @p0) AND ([Extent1].[SchemaName] LIKE @p1) AND ([Extent1].[Name] LIKE @p2)) OR

    (([Extent1].[CatalogName] LIKE @p3) AND ([Extent1].[SchemaName] LIKE @p4) AND ([Extent1].[Name] LIKE @p5)) OR (([Extent1].

    [CatalogName] LIKE @p6) AND ([Extent1].[SchemaName] LIKE @p7) AND ([Extent1].[Name] LIKE @p8)) OR (([Extent1].[CatalogName]

    LIKE @p9) AND ([Extent1].[SchemaName] LIKE @p10) AND ([Extent1].[Name] LIKE @p11)) OR (([Extent1].[CatalogName] LIKE @p12)

    AND ([Extent1].[SchemaName] LIKE @p13) AND ([Extent1].[Name] LIKE @p14)) OR (([Extent1].[CatalogName] LIKE @p15) AND

    ([Extent1].[SchemaName] LIKE @p16) AND ([Extent1].[Name] LIKE @p17)) OR (([Extent1].[CatalogName] LIKE @p18) AND ([Extent1].

    [SchemaName] LIKE @p19) AND ([Extent1].[Name] LIKE @p20)) OR (([Extent1].[CatalogName] LIKE @p21) AND ([Extent1].

    [SchemaName] LIKE @p22) AND ([Extent1].[Name] LIKE @p23)) OR (([Extent1].[CatalogName] LIKE @p24) AND ([Extent1].

    [SchemaName] LIKE @p25) AND ([Extent1].[Name] LIKE @p26)) OR (([Extent1].[CatalogName] LIKE @p27) AND ([Extent1].

    [SchemaName] LIKE @p28) AND ([Extent1].[Name] LIKE @p29)) OR (([Extent1].[CatalogName] LIKE @p30) AND ([Extent1].

    [SchemaName] LIKE @p31) AND ([Extent1].[Name] LIKE @p32)) OR (([Extent1].[CatalogName] LIKE @p33) AND ([Extent1].

    [SchemaName] LIKE @p34) AND ([Extent1].[Name] LIKE @p35)) OR (([Extent1].[CatalogName] LIKE @p36) AND ([Extent1].

    [SchemaName] LIKE @p37) AND ([Extent1].[Name] LIKE @p38)) OR (([Extent1].[CatalogName] LIKE @p39) AND ([Extent1].

    [SchemaName] LIKE @p40) AND ([Extent1].[Name] LIKE @p41)) OR (([Extent1].[CatalogName] LIKE @p42) AND ([Extent1].

    [SchemaName] LIKE @p43) AND ([Extent1].[Name] LIKE @p44)) OR (([Extent1].[CatalogName] LIKE @p45) AND ([Extent1].

    [SchemaName] LIKE @p46) AND ([Extent1].[Name] LIKE @p47)) OR (([Extent1].[CatalogName] LIKE @p48) AND ([Extent1].

    So now the EF designer is essentially unusable for updating this model from the database.  We'll have to switch to a 3rd-party tool (Hugati seems to work fine with large models).  Is this fixed in EF5.0 or some service pack for visual studio 2012? 

    Friday, September 14, 2012 11:51 PM

Answers

  • Hi,

    I spoke to a couple of guys on the team and this is not an issue that any of them are familiar with, as in it has not been reported to the EF team before.

    So the first thing is can you go and create an issue on codeplex for this problem? any extra stats or information to help reproduce the issue would be good. http://entityframework.codeplex.com/WorkItem/Create?ProjectName=entityframework. If not then I will create the issue myself with what you have here, but if you do it then you will get notified if it is fixed and things like that.

    Once that is created then this issue should get triaged and fixed, or at least improved. Of course that doesn't help your immediate issue as once it is fixed you would have to wait for the next release of EF, or use a potentially unsupported build.

    So the only really obvious way is to split your model back up. It is not ideal but you have probably still reduced the overall number of models if this one is not merged as much right?


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    • Marked as answer by pk55 Monday, September 17, 2012 6:50 PM
    Monday, September 17, 2012 6:36 PM

All replies

  • Hi Pk55,

    Welcome to the MSDN forum.

    Could you please let me know the specific line where exception was thrown?

    I find some suggestions on this page: http://blogs.msdn.com/b/emeadaxsupport/archive/2009/09/01/how-to-fix-sql-error-too-many-parameters-were-provided-in-this-rpc-request.aspx

    Also, you can check this page: http://stackoverflow.com/questions/656167/hitting-the-2100-parameter-limit-sql-server-when-using-contains

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Monday, September 17, 2012 6:09 AM
  • So now the EF designer is essentially unusable for updating this model from the database.  We'll have to switch to a 3rd-party tool (Hugati seems to work fine with large models).  Is this fixed in EF5.0 or some service pack for visual studio 2012? 

    Hi,

    You're right, Huagati DBML/EDMX Tools (my tool :) ) should allow you to update a large model, and does allow incremental (and selective) model/db changes.

    That said, there is another aspect you may want to consider; large models can have a bit of runtime performance impact. Initializing the object context can be expensive for large models, and that cost can sometimes be very noticable when running the app. Typically, this effect starts to be noticable at around 100 entities and then increases depending on model complexity. (Number of associations, and complexity of inheritance structures etc also have an impact, in addition to just number of entities).

    In other words, before going down the route of a large monolithic EF model, do some benchmarking to make sure you won't run into any runtime performance issues down the line...

    Best regards,
    Kristofer


     

       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4

    Monday, September 17, 2012 3:57 PM
  • There isn't any "line number"; the error occurs while using the EF designer when you update a large data model from sql server (2008r2 in this case but would happen on other verions as well).  I'm just essentially refreshing the existing model (which we combined manually from 7 other models).  The designer issues several db calls; one to first get the list of tables/views/stored procedures.  That call to sp_executesql that it makes only uses 1199 parms and executes successfully.  After hitting the "Finish" button, the designer issues a second sp_executesql call but this time, with 2400 parms.  That fails with this error message:

    Unable to generate the model because of the following exception: 'An error occurred while executing the command definition. See the inner exception for details.
     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.

    I looked at all sorts of other links including the ones you've supplied and they all relate to a user-created query or call to an SP at runtime.  This occurs in the designer and is entirely under EFs control; not ours. 

    Monday, September 17, 2012 4:52 PM
  • Thanks for your thoughts.

    We've been using separate smaller models for awhile and did first do benchmarks with a combined model before moving forward with it.  We have successfully combined the models (manually) without any noticeable performance impact with huge amounts of data.  We still have 4 separate models (down from 13) and only combined those entities that needed to be in the same EDMX.  We can also generate the precompiled EF views for the large model.  We just can't update the model from the database using EF's designer at the moment.

    We've tried your tool and it did allow refreshing a large model (so obviously you aren't calling sp_executesql with more parms than allowed) although we only used the trial version which I believe has an 80 table limit so we realy couldn't fully test it.  We may pursue that avenue if our other efforts don't pan out.

    Monday, September 17, 2012 5:03 PM
  • We've tried your tool and it did allow refreshing a large model (so obviously you aren't calling sp_executesql with more parms than allowed) although we only used the trial version which I believe has an 80 table limit so we realy couldn't fully test it.  We may pursue that avenue if our other efforts don't pan out.

    Your trial license can be changed to support more than 80 tables/entities. If you want to do further testing, just send an email to support@huagati.com with the license key and I'll make sure you get one that support enough tables/entities to work with your model.



     

       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4

    Monday, September 17, 2012 5:53 PM
  • Hi,

    I spoke to a couple of guys on the team and this is not an issue that any of them are familiar with, as in it has not been reported to the EF team before.

    So the first thing is can you go and create an issue on codeplex for this problem? any extra stats or information to help reproduce the issue would be good. http://entityframework.codeplex.com/WorkItem/Create?ProjectName=entityframework. If not then I will create the issue myself with what you have here, but if you do it then you will get notified if it is fixed and things like that.

    Once that is created then this issue should get triaged and fixed, or at least improved. Of course that doesn't help your immediate issue as once it is fixed you would have to wait for the next release of EF, or use a potentially unsupported build.

    So the only really obvious way is to split your model back up. It is not ideal but you have probably still reduced the overall number of models if this one is not merged as much right?


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    • Marked as answer by pk55 Monday, September 17, 2012 6:50 PM
    Monday, September 17, 2012 6:36 PM
  • I've created a work item:

    http://entityframework.codeplex.com/workitem/520

    I wouldn't re-split the models; I'd either update the models from the Hugati tool or I could just add a new fake model to get the updates for the tables I'm interested in and then update the big model manually.

    Thanks for all the replies. 

    Monday, September 17, 2012 6:50 PM