映射存储过程不返回一个字符串

Question

我正在尝试在实体框架中映射以下存储过程。它显然应该返回一个字符串，但我无法让它这样做。当我执行函数导入时，它映射到返回标量字符串集合的选择。如果我将其设置为 None，它只会返回一个 int。是SP吗？是否不支持选择存储过程？

ALTER PROCEDURE [dbo].[getCurrentConnection]
    @registrationID VARCHAR(100)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    SELECT TOP 1 ConnectionGUID FROM clientconnection WHERE registrationid = @registrationID ORDER BY created desc
END

生成的代码执行以下操作：

 public virtual ObjectResult<string> SPgetCurrentConnection(string registrationID)
        {
            var registrationIDParameter = registrationID != null ?
                new ObjectParameter("registrationID", registrationID) :
                new ObjectParameter("registrationID", typeof(string));
            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<string>("SPgetCurrentConnection", registrationIDParameter);
        }

我也无法从模型中删除 SP 并重新启动。我在设计师身上看不到它。它只允许我修改，并在它前面提供前缀 SP

Answer 1

通过修改 SP 以返回输出参数来解决。

ALTER PROCEDURE [dbo].[getCurrentConnection]
    @registrationID VARCHAR(100),   
    @connectionID varchar(100) OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    SELECT @connectionID = (SELECT TOP 1 ConnectionGUID FROM clientconnection WHERE registrationid = @registrationID ORDER BY created DESC)
END

然后用代码编写函数：

public string GetCurrentConnection(string accountId)
        {
            using (savitasEntities2 db = new savitasEntities2())
            {
                string cid = String.Empty;
                ObjectParameter connectionId = new ObjectParameter("connectionID", typeof(string));
                db.GetCurrentConnection(accountId, connectionId);
                return connectionId.Value.ToString();
            }
        }