title: Running store procedure using ef - not returning any results but works in ssms
body: <pre><code>Store procedure:
      ALTER PROCEDURE [dbo].[UserRecordGet]
                  @Name varchar(20) = null,
                  @ID int = 0,
                  @Email nvarchar(50) = null
      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 [id]
            ,[Username]
            ,[Password]
            ,[Type]
            ,[Role_id]
            ,[EmployeeID]
            ,[IsActive]
            ,[HomeTelephoneNumber]
            ,[Company_Alias]
            ,[AgentID]
            ,[LastLogin]
            ,[IsApproved]
            ,[LastPasswordChange]
            ,[FailedLogins]
            ,Application_Users.LockoutEnabled
            ,[OLBAccess]
            ,[MartinAccess]
            ,[MarbleAccess]
            ,[Email]
  
        FROM [Martin].[dbo].[Application_Users]
        WHERE
              ((@ID &gt;0) AND id = @ID) OR
              ((@Name IS NOT null) AND Username = @Name COLLATE SQL_Latin1_General_CP1_CI_AS) OR
              ((@Email IS NOT NULL) AND Email= @Email)
      END
  </code></pre>
  
  <p>C#:</p>
  
  <pre><code> public Task&lt;MAUser&gt; FindByIdAsync(int userId)
   {
       object[] parameters =  {
                                  new SqlParameter("@Name",SqlDbType.VarChar,20){  Value = DBNull.Value, Direction = ParameterDirection.Input },
                                  new SqlParameter("@Email",DBNull.Value){ Direction = ParameterDirection.Input, SqlDbType = SqlDbType.NVarChar,Size=50},
                                  new SqlParameter("@Id",Convert.ToInt32(userId)){  SqlDbType.Int,Direction = ParameterDirection.Input}
                              };
  
       context = new MALoginContext();
       var user = context.Database.SqlQuery&lt;MAUser&gt;("exec dbo.UserRecordGet @Name, @Email, @Id", parameters).FirstOrDefault();
       return Task.Run&lt;MAUser&gt;(() =&gt; { return user; });
   }
  </code></pre>
  
  <p>But I get null returned from that method. </p>
  
  <p>However the following returns a MAUser: </p>
  
  <pre><code> public Task&lt;MAUser&gt; FindByNameAsync(string userName)
       {
           object[] parameters =  {
                                      new SqlParameter("@Name",SqlDbType.VarChar,20){ Value = userName, Direction = ParameterDirection.Input },
                                      new SqlParameter("@Email",DBNull.Value){ Direction = ParameterDirection.Input, SqlDbType = SqlDbType.NVarChar,Size=50},
                                      new SqlParameter("@Id",SqlDbType.Int){ Value=-1,Direction = ParameterDirection.Input}
                                  };
              context = new MALoginContext();    
           var user = context.Database.SqlQuery&lt;MAUser&gt;("exec dbo.UserRecordGet @Name, @Email, @Id", parameters).FirstOrDefault();
           return Task.Run&lt;MAUser&gt;(() =&gt; { return user; });
       }
  </code></pre>