Entity framework and stored procedures

Hi.
why two different procedures but similar in their content, both with select * from table return different results for the entity framework 6.1 from database? Both have result (0) at the end. one returns an integer and the other returns a ObjectResult.

thx in advance

this is the working stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_Pessoas]
@PessoaID            integer=0,
@CDTipoPessoa        char(1)='',
@PessoaEmpresaID     integer=0,
@NomePessoa          varchar(100)='',
@Fantasia            varchar(50)='',
@CodigoExterno       integer=0            
as

if  @CodigoExterno  is  not  null
    if  @CodigoExterno>0  and  @PessoaID=0
          set  @PessoaID=DBASE.dbo.PessoasConverteCodigoExterno(@CDTipoPessoa,@CodigoExterno)

select *
  from DBASE.dbo.Pessoas (nolock)
 where (@PessoaID=0         or  PessoaID=@PessoaID)                   and
       (@CDTipoPessoa=''    or  CDTipoPessoa=@CDTipoPessoa)           and
       (@PessoaEmpresaID=0  or  PessoaEmpresaID=@PessoaEmpresaID)     and
       (@NomePessoa=''      or  NomePessoa like '%'+@NomePessoa+'%')  and
       (@Fantasia=''        or  Fantasia   like '%'+@Fantasia+'%')        
 order by NomePessoa
 
return(0)

and this is the result as expected:
var resultado = DataContextFabrica.GetDataContext().sp_Pessoas(pessoaID, "", 0, "", "", codigoExterno).FirstOrDefault();


this is then, the not working sp:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_MYUSERS]
@Opcao                 char(1)='',
@PessoaID              integer=0,
@Login                 varchar(50)='',
@Senha                 varchar(50)='',
@CodigoExterno         integer=0

as

if  @CodigoExterno  is  not  null
    if  @CodigoExterno>0  and  @PessoaID=0
          set  @PessoaID=DBASE.dbo.PessoasConverteCodigoExterno('U',@CodigoExterno)
 
select *
      
  from DBASE.dbo.PessoasLogins   a (nolock),
       DBASE.dbo.Pessoas  b (nolock)
 where a.PessoaID=b.PessoaID                and
       a.PessoaEmpresaID=b.PessoaEmpresaID  and
       b.StatusPessoa<>'9'                  and
     ((@opcao='P'  and  a.PessoaID=@PessoaID)  or
      (@opcao='L'  and  a.Login=@Login  and  a.Senha=@Senha))

return(0)

this is the result that I expected:
ObjectResult<sp_MYUSERS_Result> resultado = DataContextFabrica.GetDataContext().sp_MYUSERS(opcao, pessoaID, login, senha, codigoExterno);

and this is what I got:
Error      21      Cannot implicitly convert type 'int' to 'System.Data.Entity.Core.Objects.ObjectResult<sp_MYUSERS_Result>'      C:\Users\XXXXServicos\Main\Servicos.Dados\Repositorios\PessoasLoginRepositorio.cs      86      72      Services.Dados
Deraldo SilvaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
A Stored Procedure doesn't return values. Function does.

What I can see from your code is that's a little difference. In the first example it returns the value from the FirstOrDefault() function. The second example doesn't has a function.
0
Deraldo SilvaAuthor Commented:
HI Vitor, thx for your attention.
but, I have others stored procs without function and they works.
The function here is to return a single object while the other is for a list.

you see this code in the second SP?
if  @CodigoExterno  is  not  null
     if  @CodigoExterno>0  and  @PessoaID=0
           set  @PessoaID=DBASE.dbo.PessoasConverteCodigoExterno('U',@CodigoExterno)

if I remove the 'U' and change it to a proc parameter as in the first proc, it works. however, I dont know why!
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I need to see the PessoasConverteCodigoExterno code. Maybe that parameter it's an output parameter and if so can only accept variables and not values.
0
Managing Security & Risk at the Speed of Business

Gartner Research VP, Neil McDonald & AlgoSec CTO, Prof. Avishai Wool, discuss the business-driven approach to automated security policy management, its benefits and how to align security policy management with business processes to address today's security challenges.

Deraldo SilvaAuthor Commented:
Ok. that is.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[PessoasConverteCodigoExterno](
@CDTipoPessoa     int,
@CodigoExterno    int)
returns integer
as
begin

declare
@wCodigoInterno  integer

set  @wCodigoInterno=(
     select top 1 a.CodigoInterno
       from DBASE.dbo.PessoasMigracaoCorrelacoes a (nolock),
              DBASE.dbo.Pessoas                    b (nolock)
      where a.CodigoInterno=b.PessoaID        and
            a.CDTipoPessoa=b.CDTipoPessoa     and
                  a.CDTipoPessoa=@CDTipoPessoa      and
                  a.CodigoExterno=@CodigoExterno)

if  @wCodigoInterno  is  null
    set  @wCodigoInterno=0

return @wCodigoInterno

end
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
This is a function and not SP, that's why returns a value.
The problem is that you are passing 'U' that it's char but the function is expecting an integer "@CDTipoPessoa     int,"
In first example it works because the variable it's '' so SQL Server engine converts it to 0 (zero).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZberteocCommented:
This is more precisely what Victor is refering to:

 set  @PessoaID=DBASE.dbo.PessoasConverteCodigoExterno('U',@CodigoExterno)

first parameter is a of varchar(string) type, 'U', but it should be an integer as per function definition. If it is actually a varchar then you need to change the function definition like this:

ALTER function [dbo].[PessoasConverteCodigoExterno](
@CDTipoPessoa     varchar(10),
@CodigoExterno    int)

I used 10 but it should be of the same size as the column you compare it to, a.CDTipoPessoa in this case. Remember, this will work if the a.CDTipoPessoa column is a varchar as well. If not than keep it as is but make sure you pass an integer instead of a varchar.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.