Can't get stored procedure results with Entity Framework 6

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)?

Answer 1

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