How do I configure the fixed order for the parameters on the imported stored procedure function?

advertisements

Use EF 4.x. Imported SP from database.

Suppose MySp has 3 parameters as

Create Procedure MySp
@P1 varchar,
@P2 varchar,
@p3 int
As
Begin
......
End

Then I imported this SP as function in edmx from database. the xml for this in edmx will be something like:

<Function Name="MySp" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
  <Parameter Name="P1" Type="varchar" Mode="In" />
  <Parameter Name="P2" Type="int" Mode="In" />
  <Parameter Name="P3" Type="varchar" Mode="In" />
</Function>

It is fine. Then I continue to add something new in database, but with no change on MySp. So update from Database is daily job for dev.

but One day, when update database for other change, something change for MySp in xml: the parameters order changed!

<Function Name="MySp" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
  <Parameter Name="P3" Type="varchar" Mode="In" />
  <Parameter Name="P1" Type="int" Mode="In" />
  <Parameter Name="P2" Type="varchar" Mode="In" />
</Function>

As many code already done based on the parameter order like MyFunc(string p1, string p2, int p3), now the generated function become MyFunc(int p3, string p1, string p2). It is disaster for dev!

How to resolve this problem?


I recommend you to use Entity Developer for SQL Server. Our Update model from database does not refresh procedures if their parameters' collection is unchanged.