Entity Framework returns the wrong value from stored procedure

Question

I have this SQL Server stored procedure:

    @UserName VARCHAR(MAX),
    @Result INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT LoginID 
    FROM Personnel
    WHERE LoginID = @UserName AND Administrator = 'Y'

    SET @Result = @@ROWCOUNT

    RETURN @Result
END

When I test it I get the results back as expected, I get either 1 row or a 0 row count.

I call the stored procedure from my C# application with this code:

private void ValidateUser(string user)
{
    using (ComplaintsEntities db = new ComplaintsEntities())
    {
        var t = db.AAGetAdminStatus(user,0);
    }
}

When I test with a good user account I should get a result of 1 and if my user is not admin I should get a result of 0. When I inspect the return value in the C# code, it always returns -1. How can I get this to work?

Broadly speaking, Entity Framework dislikes the `RETURN x` keyword, and doesn't play well with it. You would do better by either using an `OUTPUT` parameter (`...@Result int OUTPUT`), or by using `SELECT @@ROWCOUNT` to output your value. — Laughing Vergil, Dec 17 '18 at 21:16
Possible duplicate of [Entity Framework - stored procedure return value](https://stackoverflow.com/questions/10339750/entity-framework-stored-procedure-return-value) — mjwills, Dec 17 '18 at 21:40
I think this is an EF6 bug. I have a similar problem but EF returns -1 each time instead of the correct value (which is returned when calling sproc from SSMS). The data is insert into the database correctly though. Disappointing. — Zeek2, Feb 12 '21 at 09:58

score 3 · Accepted Answer · answered Dec 18 '18 at 01:45

3

@UserName varchar(max)
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 count(LoginID) UserCount
  from Personnel
  where LoginID = @UserName And Administrator = 'Y'

END

Entity code:

private void ValidateUser(string user)
{
    using (ComplaintsEntities db = new ComplaintsEntities())
    {
        var t = db.AAGetAdminStatus(user).First().UserCount;
    }
}

answered Dec 18 '18 at 01:45

Mostafa Sayed

256
1
10

1

to avoid throwing an exception just use `FirstOrDefault()` method. – StepUp Dec 18 '18 at 14:06
I used First() because stored procedure will return one row and one column contains the count – Mostafa Sayed Dec 18 '18 at 14:10

score 0 · Answer 2 · answered Dec 18 '18 at 13:06

0

Mostafa's answer kind of worked. I used his SQL code to return the count but had to modify the C# code a little. Had to change it to.

var x = db.AAGetAdminStatus(user).First();

Once I did that I had what I needed.

answered Dec 18 '18 at 13:06

Perry

1,277
2
17
39

Thanks for the remider – Perry Dec 20 '18 at 15:54

Entity Framework returns the wrong value from stored procedure

2 Answers2