Strange behavior in EntityFramework with StoredProcedure?

Question

Strange behavior in EntityFramework with StoredProcedure?

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

I'm performing a search through StoredProcedures in EntityFramework and the Data field is displaying conversion error to type String .

Method

public IEnumerable GetFilteredList(ConsultaBanner filtro)
{

    string sql = "spAdmin_Banner_ConsultaBannerPeloFiltro @titulo={0},"
    sql += "@apresentaCookie={1}";
    sql += ",@vigenciaInicial={2},@vigenciaFinal={3},@tipoSistema={4}";
    return context.Banners.SqlQuery(sql
        , filtro.titulo// != null ? filtro.titulo : null
        ,filtro.apresentaCookie //!= null ? filtro.apresentaCookie : null
        ,filtro.vigenciaInicial.ToString() //!= null ? filtro.vigenciaInicial : null
        , filtro.vigenciaFinal.ToString() //!= null ? filtro.vigenciaFinal : null
        ,filtro.tipoSistema //!= null ? filtro.tipoSistema : null
        ).ToList();
}

This code above worked without the error of convert , but did not return anything so I did some tests on SQL Server and realized that fields were being passed as '' , so I changed StoredProcedure to convert '' to null and the conversion error occurred again.

StoredProcedure

alter  PROCEDURE [dbo].[spAdmin_Banner_ConsultaBannerPeloFiltro]
     @titulo as varchar(100) = null    
    ,@apresentaCookie as bit = null    
    ,@vigenciaInicial as datetime = null    
    ,@vigenciaFinal as datetime = null    
    ,@tipoSistema as tinyint = null
as    
if(@vigenciaInicial='')
begin
    set @vigenciaInicial=null;
end    
if(@vigenciaFinal='')
begin
    set @vigenciaFinal=null;
end

select      
     id ,titulo,mensagem,apresentaCookie    
    ,convert(varchar(10), vigenciaInicial, 103) [vigenciaInicial]    
    ,convert(varchar(10), vigenciaFinal, 103) [vigenciaFinal]    
    ,tipoSistema [tipoSistemaId]    
    ,case when tipoSistema = 1 then 'Prestador' 
      when tipoSistema = 2 then 'Cliente' when tipoSistema = 3 
      then 'Login' end [tipoSistema]    
from banner    
where 0 = 0     
and ( (titulo like '%' + @titulo + '%' and @titulo is not null) 
or @titulo is null)    
and ( (apresentaCookie = @apresentaCookie and @apresentaCookie is not null) 
or @apresentaCookie is null)

and ( ((vigenciaInicial >= @vigenciaInicial and vigenciaFinal <= @vigenciaFinal) 
and (@vigenciaInicial is not null and @vigenciaFinal is not null )) 
or (@vigenciaInicial is null or @vigenciaFinal is null) )    
and ( (tipoSistema = @tipoSistema and @tipoSistema is not null) 
or @tipoSistema is null)

Has anyone ever had this problem ?

Error :

The 'InitialValue' property on 'Banner' could not be set to a 'System.String' value. You should set this property to a non-null value of type 'System.DateTime'.

UPDATE

Both calls below return value after treatment of ''

exec spAdmin_Banner_ConsultaBannerPeloFiltro @titulo = null,@apresentaCookie = null,@vigenciaInicial = null,@vigenciaFinal = null,@tipoSistema = null

or

exec spAdmin_Banner_ConsultaBannerPeloFiltro @titulo = null,@apresentaCookie = null,@vigenciaInicial = '',@vigenciaFinal = '',@tipoSistema = null

Table Script

CREATE TABLE [dbo].[Usuario](
    [Usuarioid] [int] IDENTITY(1,1) NOT NULL,
    [CpfCnpJ] [varchar](20) NOT NULL,
    [Nome] [varchar](120) NOT NULL,
    [sexo] [char](1) NULL,
    [DataNascimento] [datetime] NULL,
    [senhaAcesso] [varchar](200) NOT NULL,
    [lembreteSenha] [varchar](200) NULL,
    [ativo] [bit] NULL,
    [DataCadastro] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [Usuarioid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

c# entity-framework stored-procedures

asked by anonymous 01.06.2017 / 14:51

2 answers

2

There are some problems one of them is in the of your StoredProcedure you have to remove both conversions ( convert(varchar(10) ) with this new sql :

CREATE PROCEDURE [dbo].[spAdmin_Banner_ConsultaBannerPeloFiltro]
    @titulo as varchar(100) = null,
    @apresentaCookie as bit = null,
    @vigenciaInicial as datetime = null,
    @vigenciaFinal as datetime = null,
    @tipoSistema as tinyint = null
as

select
     id
    ,titulo
    ,mensagem
    ,apresentaCookie
    ,[vigenciaInicial]
    ,[vigenciaFinal]
    ,tipoSistema [tipoSistemaId]
    ,case when tipoSistema = 1 then 'Prestador' when tipoSistema = 2 then 'Cliente' 
    when tipoSistema = 3 then 'Login' end [tipoSistema]
from banner
where 0 = 0 and ( (titulo like '%' + @titulo + '%' and @titulo is not null)
or @titulo is null)
and ( (apresentaCookie = @apresentaCookie and @apresentaCookie is not null) 
or @apresentaCookie is null)
and ( ((vigenciaInicial >= @vigenciaInicial and vigenciaFinal <= @vigenciaFinal) 
and (@vigenciaInicial is not null and @vigenciaFinal is not null )) 
or (@vigenciaInicial is null or @vigenciaFinal is null) )
and ( (tipoSistema = @tipoSistema and @tipoSistema is not null) 
or @tipoSistema is null)

Note: Your SQL is confusing, but even so it works take a deep look at whether that is the result you want.

The other point would be in the method, it needs to be configured with the types and passed the data correctly if they are null ( DBNull.Value ) and with the modifications below it brings the results of your table correctly:

public class ConsultaBanner
{
    public string Titulo { get; set; }
    public bool? ApresentaCookie { get; set; }
    public DateTime? VigenciaInicial { get; set; }
    public DateTime? VigenciaFinal { get; set; }
    public short? TipoSistema { get; set; }                
}

public IEnumerable GetFilteredList(ConsultaBanner filtro)
{

   SqlParameter pTitulo = new SqlParameter("@titulo",SqlDbType.VarChar, 100);            
   SqlParameter pApresentaCookie = new SqlParameter("@apresentaCookie",SqlDbType.Bit);
   SqlParameter pVigenciaInicial = new SqlParameter("@vigenciaInicial",SqlDbType.DateTime);
   SqlParameter pVigenciaFinal = new SqlParameter("@vigenciaFinal",SqlDbType.DateTime);
   SqlParameter pTipoSistema = new SqlParameter("@tipoSistema",SqlDbType.TinyInt);

   pTitulo.Value = (object)filtro.Titulo ?? DBNull.Value; 
   pApresentaCookie.Value = (object)filtro.ApresentaCookie ?? DBNull.Value;
   pVigenciaInicial.Value = (object)filtro.VigenciaInicial ?? DBNull.Value;
   pVigenciaFinal.Value = (object)filtro.VigenciaFinal ?? DBNull.Value;
   pTipoSistema.Value = (object)filtro.TipoSistema ?? DBNull.Value;

   String sql = "exec spAdmin_Banner_ConsultaBannerPeloFiltro @titulo,";
   sql += "@apresentaCookie,@vigenciaInicial,@vigenciaFinal,@tipoSistema";

   return this.Banner.SqlQuery(
    sql,
    pTitulo,
    pApresentaCookie,
    pVigenciaInicial,
    pVigenciaFinal,
    pTipoSistema
   )
   .ToList();
}

06.06.2017 / 02:21

Use specific data types like SMALLINT and MEDIUMINT instead of INT and mark columns with UNSIGNED is an exaggeration? Programming with sounds for android

score 0 · Accepted Answer

I tried in several ways to use the procedure according to the code informed by me in the query and Virgilio. I just converted the query to Linq to SQL

 public IEnumerable GetFilteredList(ConsultaBanner filtro)
        {

            return context.Banners.Where(b =>
                (b.titulo.Contains(filtro.titulo) | filtro.titulo == null) &
                (b.apresentaCookie == filtro.apresentaCookie | filtro.apresentaCookie == null) &
                (b.vigenciaInicial >= filtro.vigenciaInicial | filtro.vigenciaInicial == null) &
                (b.vigenciaFinal <= filtro.vigenciaFinal | filtro.vigenciaFinal == null) &
                (b.tipoSistema == filtro.tipoSistema | filtro.tipoSistema == null))
                .ToList();
        }