1

Closed

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.
Closed Jun 27, 2013 at 9:49 PM by RoMiller
See last comment

comments

RoMiller wrote Dec 3, 2012 at 10:39 PM

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

moozzyk wrote Dec 4, 2012 at 5: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, 2013 at 8: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 27, 2013 at 12:05 AM

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, 2013 at 7: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, 2013 at 10: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, 2013 at 9: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.

lajones wrote Jun 6, 2013 at 7:14 PM

Just for future reference the change referred to above is actually in MetadataCollection.cs in the private CollectionData class. The crossover point from one to the other is defined in MetadataCollection.UserSortedListCrossover.

lajones wrote Jun 7, 2013 at 12:07 AM

It does not appear to be to do with MetadataCollection.UseSortedListCrossover. And in fact I still can't repro the problem on either EF5 or EF6. I've gone up to models with 160 sprocs in them and still all the params seem to be in the correct order.

It must be something else about the model. Can we get the user to forward their EDMX and DB state at the point in time where they attempt the Update Model from DB that causes the problem?

moozzyk wrote Jun 7, 2013 at 5:02 PM

You should try it on the designer that shipped with VS2012. The new one uses EF6 and skips System.Data.Entity.Design.dll for reverse engineering db.

lajones wrote Jun 8, 2013 at 12:41 AM

That was what I meant. I've tried it on VS2012 with EF5 and the new VS with EF6. I've even tried on VS10SP1 and EF4. The result is the same (though for VS10SP1 you have to create the Function Imports by hand): the params always come out in the correct order in the SSDL, the CSDL and the generated code. I think it must be something specific to the customer's setup. So I think we do need to get their EDMX and DB state.

RoMiller wrote Jun 27, 2013 at 9:49 PM

Posted a comment on the original Connect bug asking for the repro. We'll close this issue for now but can re-open again if we get a repro.