1
Vote

Designer: Entity Framework randomly reorders imported function parameters for stored proc

description

Reactivating as we now have more details on this issue - see comments

This item was ported from a public Connect issue - https://connect.microsoft.com/VisualStudio/feedback/details/757222/entity-framework-randomly-reorders-imported-function-parameters-for-stored-proc

Problem Description:
Example: I have a stored procedure (let's call it sp_X) that receives an int and a string parameter. I add it to my entity framework model and import it as a function. The function import correctly builds a method that receives an int as the first parameter, a string as the second. This will work for months -- even after adding additional new (unrelated) function imports to the model and rebuilding multiple times -- and then, randomly after adding some new object to the model and updating model from database, I'll get a compile error because the function for sp_X's int and string parameters have been 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 this flipping behavior will return randomly for this proc. I read recently that if I drop/recreate the proc the problem will be corrected long-term but this is not an ideal solution since, in the case of a stored procedure with string, string parameters, I won't get a compile error when they flip but I will get run-time issues because the function is now sending string 1 to the string 2 parameter and string 2 to the string 1 parameter. Note: I am currently using the Entity Framework 4.0.30319.

comments

RoMiller wrote Dec 3, 2012 at 9:39 PM

At the minimum we should check the code to make sure ordering is deterministic.

moozzyk wrote Dec 4, 2012 at 4:01 AM

http://stackoverflow.com/questions/12729721/entity-framework-doesnt-compile-when-i-update-model-from-database

While we were not able to repro this a while ago I was wondering if we should not order items we get from the provider. This could however have impact for existing apps.

moozzyk wrote Feb 25 at 7:50 PM

I checked the queries that are sent to the provider for function discovery and we do sort the results by the name of the schema, the name of the procedure and the ordinal of the parameters.

lajones wrote Feb 26 at 11:05 PM

There's also something that re-generates all the FunctionImport params based on the Function params whenever Update Model from DB is called (see CreateFunctionImportCommand.UpdateFunctionImportParameters()). But I also don't see how it could have the effect of re-ordering the params.

Can we get some more details? The EDMX and the state of the DB just before the call to Update Model would be ideal.

RoMiller wrote Mar 5 at 6:17 PM

This came from a public Connect issue - I've added a link in the description.

I have asked the person who logged the Connect bug for a model that reproduces the issue. We will leave this issue active for a week before closing it. If a model is provided after a week we can always re-open it.

lajones wrote Mar 15 at 9:05 PM

Closing as the time period mentioned has expired. We can re-open if we receive a repro case.

** Closed by lajones 03/15/2013 2:05PM

RoMiller wrote May 13 at 8:44 PM

We now have more details on this issue. It sounds like it only repros once you get over a certain number of sprocs in the model. This is likely related to the switch we make in MetadataWorkspace once it gets to a certain size.

Details come from here - http://connect.microsoft.com/VisualStudio/feedback/details/785863/entity-framework-randomly-orders-parameters-for-imported-stored-procedure
I have added stored procedures to the entity framework model and created the function imports. After adding approximately 45 stored procedures the order of the parameters in the edmx file and function imports corresponds to the order of the parameters in the stored procedures. If I continue to add more than 45 stored procedures using Update Model from Database the order of the parameters in the edmx file and function imports starts to be random. This is incredibly frustrating as now all of my calls to the functions no longer compile due to the parameters being reordered. In total I need to have approximately 60 stored procedures in the model. I am using entity framework 5.