Entity Framework returns the wrong value from stored procedure

c# entity-framework-6 sql-server

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?

1
1
12/18/2018 5:11:23 AM

Accepted Answer

@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;
    }
}
3
12/18/2018 1:45:48 AM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow