Take the 2-minute tour ×

I have a stored procedure in SQL Sever 2008 like:

@FirstName As varchar(30),  
@LastName As varchar(30),   
@ID As int 

then in EF imported this SP as a function like:

public ObjectResult<Nullable<global::System.Int32>> MyFunc(global::System.String LastName,  global::System.String FirstName,Nullable<global::System.Int32> ID)

it works fine for long time.

Then I add some new thing to EF edmx with "update from database" today, the function parameter changed! it became:

public ObjectResult<Nullable<global::System.Int32>> MyFunc(global::System.String LastName,Nullable<global::System.Int32> ID,  global::System.String FirstName)

It's hardto believe it. I already have many codes to call this func and it worked fine. Now all of them are not working. Even I can manually change the parameter, but maybe it back the orginal order with the generated-code!

How to resolve this problem.

share|improve this question
add comment

3 Answers

This error occurs when using ALTER PROCEDURE on the stored Procedure, and does not appear in all cases.

We have been able to show that the parameter order is controlled by SQL and that after altering a stored proc parameter list (e.g. adding a parameter, especially one with a default value), then using 'Update Model From Database' on the Entity model, the parameter order becomes alphabetical. One possibility is that SQL has a mechanism for maintaining compatibility with compiled procs when an optional parameter is added, and this is manifesting as the observed behavior.

The only way we have been able to restore the correct parameter order is to DROP and CREATE the stored procedure, then update the model. No change to the storage model will survive the Update from database. You need to fix it in the database.

We are using SQL 2000.

share|improve this answer
add comment

As of EF4, default code generation is also based on a T4 template. By drilling into that T4 we can see how it generate codes for the function import:

foreach (EdmFunction edmFunction in container.FunctionImports)
    IEnumerable<FunctionImportParameter> parameters = 
FunctionImportParameter.Create(edmFunction.Parameters, code, ef);
    string paramList = string.Join(", ", parameters.Select(p => 
        p.FunctionParameterType + " " + p.FunctionParameterName).ToArray());
So it's exactly based on how your SSDL schema is look like under you model. For example, for the uspUpdateEmployeePersonalInfo SP in Adventureworks database:

CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
    @EmployeeID [int], 
    @Title [nvarchar](50), 
    @HireDate [datetime], 
    @RateChangeDate [datetime], 
    @Rate [money], 
    @PayFrequency [tinyint], 
    @CurrentFlag [dbo].[Flag] 

The SSDL is like the below:

<Function Name="uspUpdateEmployeePersonalInfo" Aggregate="false" 
 ParameterTypeSemantics="AllowImplicitConversion" Schema="HumanResources">
          <Parameter Name="EmployeeID" Type="int" Mode="In" />
          <Parameter Name="NationalIDNumber" Type="nvarchar" Mode="In" />
          <Parameter Name="BirthDate" Type="datetime" Mode="In" />
          <Parameter Name="MaritalStatus" Type="nchar" Mode="In" />
          <Parameter Name="Gender" Type="nchar" Mode="In" />
And it will result in this code to be generated inside the ObjectContext:

public int UpdateEmployeePersonalInfo(Nullable<global::System.Int32> employeeID, 
                                     global::System.String nationalIDNumber, 
                                     Nullable<global::System.DateTime> birthDate,
                                     global::System.String maritalStatus, 
                                     global::System.String gender)

My guess is that the order of the properties in your SSDL schema in your model has been changed and EF generates new codes to match that. So after validating that the parameters are declared in the desired order in you SP inside the database, try removing the SP from you model and update your model from database one more time and you'll see that the generated method code will be changed accordingly.

share|improve this answer
Looksi like all SPs with prarmeters now are not working becuase of the order. I have tried your suggestion many time and no solution. Wonder if any windows updae for EF to change the whole thing. –  KentZhou Nov 11 '10 at 20:24
And I even got this kind of error at server side when calling the function for SP:Invalid locale ID was specified. Please verify that the locale ID is correct and corresponding language resource has been installed. –  KentZhou Nov 11 '10 at 20:33
Did you go into your EDM file in XML mode and check the parameters order to make sure they are in the right order? –  Morteza Manavi Nov 11 '10 at 21:36
add comment

We are seeing this issue happen with lots of our stored procedure and we have not been able to identify why. Any new information on this will be a lot of help.

I have seen it happen on stored procedure with a lot of input and output parameters more consistently. Moreover on update form database command which when stored procedure is not changed and also edmx file is not changed but the designer causes the reorder of parameters.

share|improve this answer
add comment

Your Answer


