Can't get stored procedure results with Entity Framework 6

c# entity-framework entity-framework-6 sql-server stored-procedures

Question

I have a stored procedure which returns a 0 or a 1 depending on whether or not a specified email address exists in my database:

CREATE PROCEDURE [DatabaseSchema].[EmailAddressIsDuplicate] (@emailAddress nvarchar(255))
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS(
        SELECT *
        FROM [DatabaseSchema].[EmailUpdatesRegistrant]
        WHERE EmailAddress = @emailAddress
    )
        RETURN 1
    ELSE
        RETURN 0

    RETURN 0
END

GO

And I'm trying to derive the results of this stored procedure from an Entity Framework 6 database context:

using (DatabaseContext dbContext = new DatabaseContext())
{
    ObjectParameter param = new ObjectParameter("emailAddress", typeof(bool));
    var result = dbContext.EmailAddressIsDuplicate(emailAddress); 
}

I'm getting lots of errors.

Error #1: Using the code above, var result is always set to -1.

Error #2: I tried navigated to Edit Function Import and set the Returns a Collection Of to a Boolean scalar value. This throws the following error:

The data reader returned by the store data provider does not have enough columns for the query requested.

Error #3: I went back and set the Edit Function Import return value to None. Then I tried the following code from this answer:

using (DatabaseContext dbContext = new DatabaseContext())
{
    var p = new SqlParameter("@emailAddress", emailAddress);
    var result = dbContext.Database.SqlQuery<bool>("DatabaseSchema.EmailAddressIsDuplicate", p);
}

No immediate errors thrown, but I have no idea whether or not I can derive useful data from var result. Trying to cast result to bool throws the following error:

Cannot convert type 'System.Data.Entity.Infrastructure.DbRawSqlQuery' to 'bool'

Any ideas on how I can see the results of this stored procedure (0 or 1)?

1
5
9/9/2016 3:57:18 PM

Accepted Answer

You could try adding an output parameter (@result) in the stored procedure signature:

CREATE PROCEDURE [DatabaseSchema].[EmailAddressIsDuplicate]
    (@emailAddress nvarchar(255), @result bit out)
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS(SELECT *
              FROM [DatabaseSchema].[EmailUpdatesRegistrant]
              WHERE EmailAddress = @emailAddress)
       SET @result = 1
    ELSE
       SET @result = 0

    RETURN @result
END
GO

(you'll have to re-define your EF Model Function definition accordingly)

using (DatabaseContext dbContext = new DatabaseContext())
{
    ObjectParameter isDuplicate = new ObjectParameter("isDuplicate", typeof(bool)); 
    var result = dbContext.EmailAddressIsDuplicate(emailAddress, isDuplicate);

    bool emailIsDuplicate = (bool)isDuplicate.Value;.    
}

If you want to call the stored procedure directly with an out parameter you could follow this suggestion: Database.SqlQuery calling stored procedure that has multiple output parameters

1
5/23/2017 12:32:47 PM

Popular Answer

REASON - The template builder for EF (including v6) incorrectly sets the SP up as returning an INT containing the row count rather than the return value because it incorrectly calls the wrong ObjectContext.ExecuteFunction (found in the template-generated class YourDatabaseEntities that is the child of the DBContext).

Why wrong ExecuteFunction? - The result set incorrectly says the row count of changed rows rather than the return value or output parameters because it calls a different ExecuteFunction that discards the results. The flyover intellisense hint of the ObjectContext.ExecuteFunction says "Executes a stored procedure ….; discards any results returned from the function; and returns the number of rows affected by the execution" rather than the usual "Executes a stored procedure …. with the specified parameters".

WHY -1: I believe the SET NOCOUNT ON is causing the SP to return no count result and that Microsoft's ExecuteFunction returns that as error code.

SP FIXES - 1) You have to comment out SET NOCOUNT ON . 2) You have to change stored procedure to do the SELECT command as last statement instead of the RETURN command.

SOLUTION FIX - 1) After fixing SP, delete SP from Function Imports folder and the Data Store's SP folder. 2) Reload the SP into the EDMX by using the "Update Model from Database" 3) Rebuild all of your data project where the EDMX resides. 4) Exit Visual Studio and return. 5) Rebuild overall solution.

See: Entity Framework (Database first) has incorrect return result from stored procedure



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow