Trouble getting simple SP to work

Dec 19, 2014 at 10:12 AM
I got a SP that has one output param and a string param.

[dbo].[Sp_NextReference]
@NextNumber int OUTPUT,
@KeyName varchar(60)
AS
...
Can it be done using this? if so how?
Coordinator
Dec 21, 2014 at 9:22 AM
Mapping stored procedures with output parameters is described in this post: http://blog.3d-logic.com/2014/08/11/the-beta-version-of-store-functions-for-entityframework-6-1-1-code-first-available/

Thanks,
Pawel
Dec 21, 2014 at 10:41 AM
Thanks,
I did look into this port.
I have a stored procedure with an output param first and a input second. Then I get the error that an output param in expected for the second param
Any suggestions?
Coordinator
Dec 22, 2014 at 7:32 AM
Please post a minimal but complete repro, so that I could investigate (including the stored procedure - I don't really care about the body of the stored procedure - this is not important for mapping - it can retrurn just dummy/empty resultset and set the values of the output parameters).

Thanks,
Pawel
Jan 21, 2015 at 1:30 PM
Hi Pawel,

Code snippet:
    [DbFunction("BPDbContext", "Sp_NextReference")]
    [DbFunctionDetails( DatabaseSchema="dbo" )]
    public virtual ObjectResult<int> Sp_NextReference(
        [ParameterType(typeof(int))]System.Data.Entity.Core.Objects.ObjectParameter nextNumber,
        [ParameterType(typeof(string))]System.Data.Entity.Core.Objects.ObjectParameter tableName)
    {
        return ((System.Data.Entity.Infrastructure.IObjectContextAdapter)this).ObjectContext.ExecuteFunction<int>("Sp_NextReference", tableName, nextNumber);

    }
the SP is the following:
CREATE PROCEDURE [dbo].[Sp_NextReference]
@NextNumber int OUTPUT,
@TableName varchar(60)
AS

DECLARE
/ sp_PKeys output /
@PKeys_Column_Name SYSNAME
,@SQL VARCHAR(255)

SELECT @NextNumber = 0

SELECT @NextNumber = NEXTREFERENCE + 1
FROM ReferenceByTableName
WHERE TableName = @TableName

is this sufficient?
Coordinator
Jan 21, 2015 at 9:35 PM
I believe this is because the convention always treats parameters of ObjectParameter type as output parameters. Try changing the second parameter of your method to string:
    ...
    public virtual ObjectResult<int> Sp_NextReference(
        [ParameterType(typeof(int))]System.Data.Entity.Core.Objects.ObjectParameter nextNumber, string tableName)
    ...
Hope this helps,
Pawel
Jan 22, 2015 at 2:31 PM
Hi Pawel,

Type must always be an ObjectParameter when passing into ExecuteFunction.

ObjectParameter is always handled as a InputOutput.

return ((System.Data.Entity.Infrastructure.IObjectContextAdapter)this).ObjectContext.ExecuteFunction<int>("Sp_NextReference", tableName, nextNumber);

How can I do what you propose above? Can you provide me an example?
Coordinator
Jan 22, 2015 at 8:58 PM
Sorry for not posting the entire example. You convert your input parameter inside the store function method stub like this:
[DbFunction("BPDbContext", "Sp_NextReference")]
[DbFunctionDetails( DatabaseSchema="dbo" )]
public virtual ObjectResult<int> Sp_NextReference(
        [ParameterType(typeof(int))]System.Data.Entity.Core.Objects.ObjectParameter nextNumber, string tableName)
    {
        var tableNameParameter = tableName != null ?
            new ObjectParameter("TableName", tableName) :
            new ObjectParameter("TableName", typeof(string));

        return ((System.Data.Entity.Infrastructure.IObjectContextAdapter)this).ObjectContext.ExecuteFunction<int>("Sp_NextReference", tableNameParameter, nextNumber);

    }
(you can find a working example in https://codefirstfunctions.codeplex.com/SourceControl/latest#CodeFirstStoreFunctionsTests/E2ETests.cs)

ObjectParameter is treated by EF as InOut indeed but the convention uses function signature to build part of the EF model responsible for store functions and it needs to be able to recognize out parameters. If it did not use the ObjectParameter type for this I would have had to introduce yet another attribute for arguments passed to store function stubs. Note that it is quite unusual to pass input parameters as ObjectParameter to a store function stub method for a couple of reasons - 1) maintainability - if you need to invoke a stored procedure from multiple places you need to convert arguments to ObjectParameter arguments in multiple places, 2) readability - once you converted your argument to an ObjectParameter instance you no longer see what type it is. Note that for input parameters there is no technical reasons that they should be visible outside the store function stub method so converting a parameter to ObjectParameter before they are needed does not make a lot of sense. It is the opposite for output parameters - you can't read a value (at least if you are using SqlServer) of an output parameter until you first have consumed the resultset returned by the stored procedure. Since typically you don't want to consume the resultset in the store function stub method you need the ObjectParameter instance passed to the store function stub method alive until the resultset has been consumed or you won't be able to read the value.

Hope this helps,
Pawel
Jan 23, 2015 at 9:29 AM
Edited Jan 23, 2015 at 10:26 AM
Pawel

thnx for the clarification.

still not getting there:
when calling the ExecuteFunction I get an EntityCommandExecutionException:
{"The data reader returned by the store data provider does not have enough columns for the query requested."}

However the Procedure is executed correctly on the server
Jan 23, 2015 at 11:34 AM
Edited Jan 23, 2015 at 11:34 AM
Stored procedure support has now been added to Entity Framework reverse POCO generator let it generate the mappings for you.

I've also written a blog post about manually adding support for stored procedures to entity framework: http://simon-hughes.blogspot.co.uk/2015/01/calling-sql-server-stored-procedures.html
Jan 23, 2015 at 12:56 PM
Thnx a million simon,

It does the trick...

Pawel,

i'm still curious why it does not doe what it should do.
Coordinator
Jan 23, 2015 at 4:01 PM
I did not look closely at the stored procedure before - I believe this is because EF is expecting one column (the result returned from your stored furnction stub method is ObjectQuery<int>) and your stored procedure does not return anything. You would have to return an empty resultset (dummy) with one column. I am not sure EF can handle stored procedures that do not return anything since usually you want to materialize and map the results to some objects (entities or complex type objects) and therefore EF expects the results to have a certain shape even if the resultset is empy.

Hope this helps,
Pawel