Stored procedure parameter names and Entity Framework

Question

I've generated an Entity Framework model from an SQL Server 2005 database and started to import stored procedures. So far so good, but one of the stored procedures throws an exception when I try to run it:

Procedure or function 'csp_getCoworker' expects parameter '@@firstname', which was not supplied.

Here is the signature for the stored procedure:

ALTER PROCEDURE [dbo].[csp_getCoworker](
@@firstname nvarchar(32),
@@lastname nvarchar(32),
@@businessarea nvarchar(512),
@@location nvarchar(512)
)

And here is the code generated by Entity Framework

ObjectParameter p_firstnameParameter;
if (p_firstname != null)
{
    p_firstnameParameter = new ObjectParameter("p_firstname", p_firstname);
}
    else
{
     p_firstnameParameter = new ObjectParameter("p_firstname", typeof(global::System.String));
}
[...]
return base.ExecuteFunction<csp_getCoworker_Result2>("csp_getCoworker", p_firstnameParameter, p_lastnameParameter, p_businessareaParameter, p_locationParameter);

Is it the double @-characters in the parameter name that's messing things up?

Ladislav Mrnka · Accepted Answer · 2011-05-01 13:26:10Z

2

I just tested this because it is very uncommon scenario.

The stored procedure follows very strange naming convention because @@ should not be used. It is by default used by some SQL server system variables and should not be used for anything else. Using @x as parameter of stored procedure defines a parameter x but using @@x will define parameter @x - @ is part of parameter name!

This is a very big problem in C# because @ is escape character used to define names of variables same as reserved keywords. For example:

string @string = "abc";

defines variable named string. Variable name cannot start with @.

Entity framework deals with this by replacing all @ with _ and prefixing parameter's name with p directly in SSDL. The reason is that function imports created from SSDL mapped stored procedure must have parameters with the same name as the procedure but in the same time @ is not allowed starting character. If you try to modify EDMX manually it will fail its own XSD validation because @ is not allowed starting character for identifiers defined in CSDL.

Even this can probably be considered as a bug it is more about missing possibility to change the name of the parameter in CSDL. At the moment this is by design and only way is to correct names of parameters in SQL stored procedure.

answered May 1, 2011 at 13:26

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bartek Tatkowski Over a year ago

This was a great answer! It gave me a push in the right direction and I resolved the issue by writing wrapper procedures with correctly named variables that just passed them to the appropriate SP.

Matti Virkkunen Over a year ago

@Bartek: What you should have done is find whoever decided it was a good idea to use double @-marks in their parameters names, slap them in the face, and then make them fix it.

Bartek Tatkowski Over a year ago

@Matti: I tried that, but they've since long left the organization. >:(

Collectives™ on Stack Overflow

Stored procedure parameter names and Entity Framework

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related