alex alex - 3 months ago 58
C# Question

Can't get stored procedure results with Entity Framework 6

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

You could Try adding a return parameter (result) in the sProc. 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