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


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


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


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 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, 2013 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, 2013 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, 2013 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, 2013 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.

lajones wrote Jun 6, 2013 at 6: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 6, 2013 at 11:07 PM

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 4: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 7, 2013 at 11:41 PM

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 8: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 8:49 PM

See last comment

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

indigosquared wrote Dec 16, 2014 at 3:42 PM

Previous post accidental...


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):

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

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 Dec 17, 2014 at 3:33 PM

Reopening to give the repro steps provided a try.

indigosquared wrote Dec 17, 2014 at 11:21 PM

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.

indigosquared wrote Jan 9 at 7:37 AM

Apologies for the lack of update. I was busier that expected to the end of last year and then on leave.

A little more information...

I've taken a copy of the troublesome database and removed all data. Problem still exists (as expected).
I wrote and ran a script to drop all procedures except the ones used by the EDMX. Problem resolved.

The issue is definitely linked to the number of schema items in the database.
I'm trying to find an easy way of renaming all the database objects whilst retaining this issue so that I can send you the database.

Any ideas appreciated.

Happy New Year!

lajones wrote Feb 18 at 4:17 PM

@indigosquared Apologies for the long delay. Unfortunately my computer says the attached .zip contains a virus and is refusing to open it. Could you attach at least INFORMATION_SCHEMA.sql as a separate file please?

indigosquared wrote Feb 19 at 9:10 AM

@lajones No problem. The zip has been virus checked using Sophos with nothing reported.
I'll upload all the files individually here in separate posts.

indigosquared wrote Feb 19 at 9:10 AM


indigosquared wrote Feb 19 at 9:11 AM


indigosquared wrote Feb 19 at 9:11 AM

3 - EF Query

indigosquared wrote Feb 19 at 9:11 AM

4 - EF Query Output

indigosquared wrote Feb 19 at 9:12 AM

5 - EDMX Before

indigosquared wrote Feb 19 at 9:12 AM

6 - EDMX After

lajones wrote Feb 19 at 7:20 PM

@indigosquared Sorry about that - turned out my virus checker was calling everything a virus!

Anyhow unfortunately I'm still unable to repro the problem. However I can see how it could happen if you somehow managed to get the INFORMATION_SCHEMA.PARAMETERS table in a state where the result when you perform a simple query from the view is different depending on whether you include an ORDER BY. Unfortunately I've been unable to get my INFORMATION_SCHEMA.PARAMETERS table into that state.

But I'd like to try to figure out what's happening on your machine. I've attached a SProcedureParametersDefiningQuery.sql file. There's a line near the bottom that says "ORDER BY ParentId, Ordinal". Could you comment out that line and execute the query and then put it back in and execute again and send me the 2 results? You can contact me at lajones at microsoft dot com. Thanks.

lajones wrote Feb 19 at 11:08 PM

Also I've attached EFQueryBeingExecuted150219_1437.sql which is equivalent to your EF Query.sql file but I added code to actually output the Ordinal on which it is ordering. Could you execute that too and send me the results? (There is an ORDER BY clause at the bottom of EF Query.sql which should ensure that the params are returned in Ordinal order - so I'm trying to investigate why that does not appear to be working on your box).

lajones wrote Feb 26 at 6:11 PM

EF Query - NO sp_executesql.sql

lajones wrote Feb 26 at 6:11 PM

EF Query - Including Ordinal.sql

lajones wrote Feb 26 at 6:24 PM

I've attached two SQL queries from @indigosquared that he and I used to track down this issue. It turned out that stripping out the sp_executesql, the big WHERE clause and the parameter definitions and values that were passed to sp_executesql we could still see the same bad-order effect on his box but not on mine (EF Query - NO sp_executesql.sql). Note that the query does contain an ORDER BY clause on the correct (Ordinal) column. However if we also included that Ordinal column in the SELECT clause then the ordering issue went away (EF Query - Including Ordinal.sql).

This helped us trace the issue to this SQL Server issue https://msdn.microsoft.com/en-US/library/ms178653(SQL.90).aspx . Specifically see the section titled "Differences Between Lower Compatibility Levels and Level 90". @indigosquared had a database which was upgraded from an earlier version of SQL Server (rather than a new install) and hence that database was running with an old compatibility level.

To diagnose this run:

sp_dbcmptlevel '<your_database_name>'

If it reports a value below 90 then running

sp_dbcmptlevel '<your_database_name>', 90

updates the compatibility level of the database and fixes the problem.