存储过程对于实体框架中的所有情况返回-1

CREATE PROC spIsValidUser
     @UserName varchar(50),
     @Password varchar(50) 
AS
    IF  Exists(SELECT * FROM Users where UserName=@UserName and Password=@Password)
    BEGIN
        return 0

    END
    ELSE
    BEGIN
        return 1
    END
 GO

I have created this Stored Procedure and tring to call this Stored Procedure using entity framework. Below is code in written in C#.

我创建了这个存储过程，并尝试使用实体框架调用这个存储过程。下面是用c#编写的代码。

MyBusEntities db = new MyBusEntities();
int empQuery = db.spIsValidUser("abc", "abc@123");

spIsValidUser Stored Procedure return -1 in all case. Please let me know error.

spIsValidUser存储过程返回-1。请告诉我错误。

EDIT - According to given answer, Store procedure is not used return statement because Entity Framework cannot support Stored Procedure Return scalar values out of the box..Let me know how can I send scalar data from Stored Procedure?

编辑——根据给定的答案，存储过程不使用返回语句，因为实体框架不能支持存储过程返回标量值。让我知道如何从存储过程发送标量数据?

3 个解决方案

#1

Your stored procedure is currently returns a scalar value. Use the following steps to solve this issue:

存储过程当前返回一个标量值。使用以下步骤来解决这个问题:

Change your stored procedure like this (Don't use the keyword return in the stored procedure to return the value, Entity Framework cannot support Stored Procedure Return scalar values out of the box. BUT there is a work around):

更改您的存储过程(不要在存储过程中使用关键字返回来返回值，实体框架不能支持存储过程返回标量值。但是有一项研究):
```
ALTER PROC spIsValidUser
@UserName varchar(50),
@Password varchar(50) 
AS
SELECT Count(*) FROM Users where UserName= @UserName and Password= @Password
return
```
You need to Import the stored procedure as a Function. Right-click on the workspace area of your Entity model and choose Add -> Function Import.

您需要将存储过程作为函数导入。右键单击实体模型的工作区区域，选择Add ->函数导入。
In the Add Function Import dialog, enter the name you want your stored procedure to be referred to in your model, choose your procedure from the drop down list, and choose the return value of the procedure to be Scalar.

在Add Function Import对话框中，输入您希望在模型中引用存储过程的名称，从下拉列表中选择过程，并选择过程的返回值为标量。

Finally write you code like this:

最后这样写代码:

MyBusEntities db = new MyBusEntities();
System.Nullable<int> empQuery = db.spIsValidUser("abc", "abc@123").SingleOrDefault().Value;
MessageBox.Show(empQuery.ToString());// show 1 if Exist and 0 if not Exist

Edit: I think support of stored procedure return values depends on version of Entity framework. Also Entity Framework doesn't have rich stored procedure support because its an ORM, not a SQL replacement.

编辑:我认为支持存储过程返回值取决于实体框架的版本。此外，实体框架没有丰富的存储过程支持，因为它是ORM，而不是SQL替换。

#2

Have you imported your stored procedures in the EF model correctly? and are you setting correct return type to stored procedures??

是否正确地导入了EF模型中的存储过程?您是否为存储过程设置了正确的返回类型?

There are 4 possible return type that you can set to your procedures The possible return types are:

您可以为您的程序设置4种可能的返回类型，可能的返回类型是:

none
没有一个
Scalars
标量
Complex
复杂的
Entities
实体

you need to set scalars return type.

您需要设置标量返回类型。

if you dont know how to set the return type, then here is the full tutorial http://www.binaryintellect.net/articles/30738a7c-5176-4333-aa83-98eab8548da5.aspx

如果您不知道如何设置返回类型，那么这里有完整的教程http://www.binaryintelligent .net/articles/30738a7c- 5176-4333-3 - aa88eab8548da5.aspx

quick example.

简单的例子。

CREATE PROCEDURE CustOrderCount
    @CustomerID nchar(5)
AS
BEGIN
    SELECT COUNT(*) FROM ORDERS 
        WHERE CUSTOMERID=@CustomerID;
END


NorthwindEntities db = new NorthwindEntities();
var count = db.CustOrderCount("ALFKI");
int ordercount = count.SingleOrDefault().Value;

this will return int order count.

这将返回int顺序计数。

#3

Have you tried:

你有试过:

CREATE PROC spIsValidUser
     @UserName varchar(50),
     @Password varchar(50) 
AS
    IF  Exists(SELECT * FROM Users where UserName=@UserName and Password=@Password)
    BEGIN
        SELECT 0

    END
    ELSE
    BEGIN
        SELECT 1
    END
 GO

#1