none
Entity Framework re-orders stored procedure input parameters after an update model from database RRS feed

  • Question

  • Example:  I have a stored procedure (let's call it sp_X) that receives an int and a string parameter.  The function import builds a method that receives an int as the first parameter, a string as the second.  This will work for months and then I add some new object to the model, do an update model from database, and get a compile error because sp_X's int and string parameters have flipped .... it's now sp_X(string, int).  No changes have been made to the proc (sp_X).

    I have to remove the proc and function from the model and reload both in order to correct the problem -- but it will return randomly for this proc.  I read recently that if I drop/recreate the proc the problem will be corrected long-term.

    This is sort of bearable because I can catch it at compile.  BUT, another proc that takes a string and a string will flip those parameters too... this is not caught during compile so now I end up with run-time problems because queries that are searching by param 1 have a param 1 value that is actually param 2's value and vice versa.

    I can find only one bit of documentation on this (the reference to the drop/recreate proc) but this cannot be the solution .... you could end up have brand new run-time errors every time you touch the model.

    This is enough to make me abandon what I have up until now considered a sweeeeeeet framework -- does anyone know if this problem has been resolved in a later version of the framework?  I don't see it mentioned in any of the release notes.  Note: I am currently using the Entity Framework 4.0.30319.


    Thursday, July 26, 2012 9:22 PM

Answers

All replies

  • Hi Patti,

    It's a really strange problem.   I tried to repro it but I failed and I never such a problem before.   Besides, I am using the same version of .NET Framework and Entity Framework as you mentioned.  Could you please give us more information about the stored procedure?  Does it contain any OUTPUT parameter?  Could you share a sample for us to do further investigation? 

    Good day!

    Thanks


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

    Monday, July 30, 2012 2:19 AM
    Moderator
  • Hi Michael,

    Thanks for responding.  The problem is not easy to reproduce.  Here are some details:

    My model includes 110 stored procedures imported as functions and mapped to complex objects.  

    At first, the parameter switching occurred only on a single stored procedure (always the same one) which takes two input parameters (an int and a varchar(100)), no output parameters, and returns the results of a select statement -- nothing fancy about the select except that it includes a "partition by" statement (I can't imagine that is relevant).

    Because this particular procedure receives int, string -- which was flipped to string, int, existing method calls to the imported function would cause a compile error and so it was easy to notice there was a problem.  However, nothing had been changed on the stored procedure between the last compile and this compile -- the only change was that some new stored procedure had been added to the model -- I hope that makes sense... but to further clarify: Let's say I added this particular stored procedure in January and everything compiled fine.  And for the sake of example let's say I added 1 new stored procedure to the model each month and each time everything compiled fine and then in June I added another new proc and got a compile error on the proc that had been added in January (on which nothing had been changed) other than the framework switching the parameter order....  

    The error occurs randomly like this every 15th compile or so and up until a week ago it was ONLY this single proc.  Then, a week ago a different proc had the same problem -- only this time the proc had a string/string parameter.  Just like the first one, nothing had been changed in the proc and it had been in production and working fine for months.  In this case, however, because it was a string/string parameter, there was no compile error but when the method calling the imported function ran in production (let's say it passed values for @FirstName, @CompanyName) -- the method passed the search values for first name and company name but the framework's imported function switched them and sent the proc @FirstName=company name, @CompanyName=first name -- which, of course, found no search results.

    Anything you can offer in terms of help would be very much appreciated.

    Thank you.

    Patti


    Patti Steiner

    Tuesday, July 31, 2012 8:06 PM
  • Hi Patti,

    Without reproducing the issue and also the it occurs randomly, so I cannot say for sure where the problem is.  However, it should be considered as a product issue, so I would recommend you open a bug report at Microsoft Connect, http://connect.microsoft.com 

    As a workaround, you may consider write a small program to check the stored procedure parameters order after each recreating the model.   For parsing the .edmx file, you can use LINQ to XML since .edmx file is XML based.   For getting the stored procedure parameters, you may refer to http://stackoverflow.com/questions/3038364/get-stored-procedure-parameters-by-either-c-sharp-or-sql

    Good day!

    Thanks


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

    Wednesday, August 1, 2012 2:15 AM
    Moderator
  • Hi Patti,

    This post "Entity Framework re-orders stored procedure input parameters after an update model from database" was written around three years ago, but I am having this same problem. My project was working very well, but today when I tried to update model from database to add a new procedure it changed the order of parameters of many procedures. This error has occurred 6 months ago too in the same project. That time I restored the old edmx file from few backups and I could resolve it. But now it occurred again. I don't know how to get rid of this.

    Were you able to have any solution for this issue?

    Any help is much appreciated. Thanks in advance.

    Looking forward to hear from you.

    Kind Regards,

    Asha Khatri

    Saturday, June 27, 2015 9:21 AM
  • Hello Asha,

    Unfortunately I never found a resolution for this.  I found the issue impossible to recreate reliably -- there were no repeatable steps that guaranteed the problem would resurface.  I could make dozens of changes to the model (and interestingly, always in other procs, not the one that was affected) and everything would be fine and then it wasn't.

    After posting here, I reported the problem to our Microsoft Partner Program rep and he provided contact information for reporting a bug but indicated that if it were not deemed a bug we would have to pay for support.  

    I don't think we pursued it beyond that and, unfortunately I have stopped using the framework for binding procs to complex objects because our customers have a low tolerance for run-time errors.

    Sorry I couldn't have been more help.  Are you using the most current version of the framework? Perhaps that will make a difference.


    Patti Steiner

    Monday, June 29, 2015 3:23 PM