CODEWITHSUNDEEP
c#entity-frameworkstored-procedures
user2471435
user2471435

Reputation: 1674

Entity Framework: Unable to call stored procedure, wrong code generation

I have a working stored procedure which works when called from old ADO.NET. The following is it:

GO
ALTER procedure [dbo].[GetParentID]
    @SSHIP_AppID as varchar(50),
    @ParentID as varchar(150) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ParentID = a.iBuild_GUID

    FROM dbo.XRef_iBuild_SSHIP as a
    WHERE a.SSHIP_appId = @SSHIP_AppID
    AND a.SSHIP_appId <> ''
END

In ADO.NET, it takes an appID and simply returns a string parentId:

 private string GetParentId(string appId)
        {
            var connection = new SqlConnection();
            string parentId = String.Empty;
            try
            {
                connection.ConnectionString = "Data Source="blah...";
                var command = new SqlCommand("GetParentId", connection);

                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@SSHIP_AppID", appId));


                SqlParameter parent = new SqlParameter("@ParentID", SqlDbType.VarChar, 255);
                parent.Direction = ParameterDirection.Output;
                command.Parameters.Add(parent);

                connection.Open();

                command.ExecuteNonQuery();

                parentId = (command.Parameters["@ParentID"].Value).ToString();
            }
            catch (Exception ex)
            {
                Logger.LogError(appId, ex.ToString(), "Interface12 - Cannot get ParentId", null, "", 0, "Interface12");
            }
            finally
            {
                connection.Close();
            }
            return parentId;
        }

Here's what the EF generates when I do a Function Import of the Stored Procedure:

 public virtual int GetParentID(string sSHIP_AppID, ObjectParameter parentID)
        {
            var sSHIP_AppIDParameter = sSHIP_AppID != null ?
                new ObjectParameter("SSHIP_AppID", sSHIP_AppID) :
                new ObjectParameter("SSHIP_AppID", typeof(string));

            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("GetParentID", sSHIP_AppIDParameter, parentID);
        }

Is this correct? If so, can I have some sample code to work with getting the parentID? If not, how can I have it generate something better?

Upvotes: 2

Views: 699

Answers (1)

Zach
Zach

Reputation: 3207

Looks correct to me. Because you can have multiple outputs, and a C# function cannot return more than one thing, it's returning the output in an ObjectParameter. I believe this is the only way it will generate it. If you generate a function call instead of an SP call, perhaps it will generate it with a return type of string (I haven't tried), but this is to support multiple output parameters. If you like, you can wrap this generated function with your own that calls this function and returns the parentID parameter value.

private string GetParentId(string appId)
{
   ObjectParameter parentID = new ObjectParameter("ParentID", typeof(string));
   GetParentID(appId, parentID);
   return parentID.Value.ToString();
}

Upvotes: 2

Related Questions