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.

file attachments

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.

RoMiller wrote Jun 27, 2013 at 9:49 PM

See last comment

** Closed by RoMiller 06/27/2013 1:49PM

indigosquared wrote Tue at 4:42 PM

Previous post accidental...

Anyway:

We are also experiencing this issue currently.. with a large database that is growing fast with schema changes.
Hopefully the following information will help towards a solution.

I've attached a zip file containing the following (note that all procedure and parameter names have been changed for security purposes):

1 - INFORMATION_SCHEMA.sql
This is a SQL statement that returns a list of parameters for "Procedure30" in our database, including their position.

2 - INFORMATION_SCHEMA Output.xlsx
This is the output of the above SQL (1).
Note the parameter order.

3 - EF Query.sql
This is the query executed by Entity Framework when refreshing our model from the database. (Traced using SQL Profiler)

4 - EF Query Output.xlsx
This is a subset of the output of the query above relevant to "Procedure30".
Note that the parameter order is incorrect.

5 - EDMX Before.xml
This is a subset of our EDMX before the refresh.
Note that the order matches the stored procedure definition from INFORMATION_SCHEMA (Parameter16 is new so not included)

6 - EDMX After.xml
This is a subset of our EDMX after the refresh.
Note that the parameter order is now jumbled, but reflects the output of the SQL above (3).

It looks like the issue lies in the fact that the EF query isn't specifically including the parameter position when ordering the results.

divega wrote Wed at 4:33 PM

Reopening to give the repro steps provided a try.

indigosquared wrote Today at 12:21 AM

Thanks for re-opening. We've worked around the issue by using named parameters when calling the EDMX functions which has taken a fair amount of re-work. Since it sounds like it's still an issue in EF5 and EF6, I'm keen to try and help resolve.

The database exhibiting the issue contains 218 tables, 149 views, 97 functions and 892 stored procedures. Our EDMX only contains 43 of the stored procedures; no tables, no views and no functions.

I've been attempting to get you a backup of a cut-down sample database exhibiting this issue but have so far been unsuccessful. Unfortunately, I can't send you the problem database as as it belongs to a client.

The EF model generation has been working fine for months and all the parameters were generated in the correct order.. then after recently modifying one of the 43 stored procedures the generation started jumbling the parameters up.

This database is hosted on MS SQL Server 2005 SP3 (9.0.4035). I've tried restoring the database to a 2008 R2 server (10.50.4000) but the model generation still jumbles the parameters up.

I'll attempt to get you some more information later this week.
Let me know if I can provide anything else in the mean time.