Strange Behavior in Entityframework with Storedprocedure?

Question

I’m conducting a search through StoredProcedures in the EntityFramework and the field of Data this presenting error conversion 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 above code worked without the error of convert, but did not return anything so I did some tests on SQL Server and I realized that the Tadas fields were being passed as '', so I changed the StoredProcedure to convert '' for null and the error conversion happened 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)

Someone’s been through this trouble?

The error:

The 'vigenciaInicial' Property on 'Banner' could not be set to a 'System.String' value. You must set this Property to a non-null value of type 'System.Datetime'.

UPDATE

Both calls below return value after the 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

Script Table

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]

Answer 1

Have some problems one of them is in sql of his StoredProcedure have to remove the two conversions (convert(varchar(10)) getting 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)

_{Observing: your SQL is confused, but even so it works take a deep look if this is the same result you want.}

The other point would be in the method, needs to be configured with the types and passed the data correctly if they are null (DBNull.Value) and with the modifications below 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();
}

Answer 2

I tried in several ways to use the trial according to the code informed by me in the query and by Virgilio. I ended up converting 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();
        }