Why the order of params of function imported from

问题:

I have a stored procedure in SQL Sever 2008 like:

ALTER PROCEDURE [dbo].[mySP]
@FirstName As varchar(30),  
@LastName As varchar(30),   
@ID As int 
As 
Begin
--......
End

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.

回答1:

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.

You need to fix it in the database. 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.

We are using SQL 2000.

回答2:

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" 
BuiltIn="false" NiladicFunction="false" IsComposable="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" />
</Function>

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.

回答3:

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.

回答4:

Verify that the compatibility level of the database is not below 90

sp_dbcmptlevel 'your_database_name'

If it reports a value below 90 then run

sp_dbcmptlevel 'your_database_name', 90

Thanks to lajones in Codeplex