c# - 无法使用 Entity Framework 6 获取存储过程结果

Question

我有一个返回 0 的存储过程或 1取决于我的数据库中是否存在指定的电子邮件地址:

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

我正在尝试从 Entity Framework 6 数据库上下文中导出此存储过程的结果:

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

我遇到了很多错误。

错误#1:使用上面的代码，var result始终设置为 -1。

错误 #2: 我尝试导航到 Edit Function Import并设置 Returns a Collection Of到 Boolean标量值。这会引发以下错误:

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

错误 #3: 我返回并设置了 Edit Function Import返回值 None .然后我从这个答案中尝试了以下代码:

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

没有立即抛出错误，但我不知道我是否可以从 var result 中获取有用的数据.尝试投 result至 bool抛出以下错误:

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

关于如何查看此存储过程(0 或 1)的结果有什么想法吗？

Answer 1

您可以尝试在存储过程签名中添加输出参数 (@result):

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

(您必须相应地重新定义您的 EF 模型函数定义)

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

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

如果您想直接使用 out 参数调用存储过程，您可以遵循以下建议: Database.SqlQuery calling stored procedure that has multiple output parameters