Convert Linq Query to Dapper

3

In my application I have the following query:

public IEnumerable<Cliente> BuscarClientePorAnoETipo(string ano, string filial, string tipoPrestacao)
{
     var filialConvertInt = Convert.ToInt32(filial);
     var prestacaoConvertInt = Convert.ToInt32(tipoPrestacao);

     return Find(x =>  x.AnoVigencia == ano && x.FilialId == filialConvertInt
                && x.TipoPrestacaoId == prestacaoConvertInt&& x.Ativo).ToList();
}

It works normally, and returns me the data I need. However, for performance reasons I'm modifying my selects , using Dapper in all my queries.

Not knowing the tool very well, I'm having trouble converting the query to Dapper . Based on what I read in some articles, I came to this query:

public IEnumerable<Cliente> BuscarClientePorAnoETipo(string ano, string filial, string tipoPrestacao)
{
    var filialConvertInt = Convert.ToInt32(filial);
    var prestacaoConvertInt = Convert.ToInt32(tipoPrestacao);

    const string sql = @"select * from clientes c inner join TipoPrestacao t on (c.TipoPrestacaoId = t.TipoPrestacaoId)" +
                        "inner join VencimentoPrestacoes v on (v.VencimentoPrestacaoId = t.VencimentoPrestacaoId)" +
                        "where c.AnoVigencia = @anoVigencia and" +
                        "c.FilialId = @filialId and" +
                        "c.TipoPrestacaoId = @prestacaoId" +
                        "and Ativo = 1";

    using (var cn = Connection)
    {
        cn.Open();

        var clientes = cn.Query<Cliente, TipoPrestacao, VencimentoPrestacao, Cliente>(sql, (c, t, v) =>
        {
            c.TipoPrestacao = t;
            c.TipoPrestacao.VencimentoPrestacao = v;
            return c;
        }, new{anoVigencia = ano, FilialId = filialConvertInt, prestacaoId = prestacaoConvertInt}, splitOn:"ClienteId");

        return clientes.ToList();
    }
}

However, when running, I get the following error:

Exception Details:

  

System.Data.SqlClient.SqlException: Incorrect syntax near 'andc'.

     

Source Error:

     

Line 32: cn.Open ();

     

Line 33:

     

Line 34: var clients = cn.Query (sql, (c, t, v) = >

     

Line 35: {

     

Line 36: c.TypePrestation = t;

I think it's a% error of%, but I have not found anything that can help me. When I run this code in syntax , it returns me the correct values.

My entities are as follows:

Customer

public class Cliente
{
    public int ClienteId { get; set; }
    public string Orgao { get; set; }
    public string NomeEntidade { get; set; }
    public string Responsavel { get; set; }
    public string Telefone { get; set; }
    public string TipoPendencia { get; set; }
    public string Observacao { get; set; }
    public DateTime DataCadastro { get; set; }
    public string AnoVigencia { get; set; }
    public bool Ativo { get; set; }

    public int UsuarioId { get; set; }
    public int EstadoId { get; set; }
    public int CidadeId { get; set; }
    public int TipoPrestacaoId { get; set; }
    public int FilialId { get; set; }
    public int EnvioPrestacaoId { get; set; }

    public virtual Usuario Usuario { get; set; }
    public virtual Cidade Cidade { get; set; }
    public virtual Estado Estado { get; set; }
    public virtual TipoPrestacao TipoPrestacao { get; set; }
    public virtual Filial Filial { get; set; }
    public virtual EnvioPrestacao EnvioPrestacao { get; set; }
}

TypeType

public class TipoPrestacao
{
    public int TipoPrestacaoId { get; set; }
    public string Descricao { get; set; }
    public string TipoVencimento { get; set; }
    public string ResponsavelTCE { get; set; }
    public string ContatoTCE { get; set; }
    public int? QuantidadeArquivos { get; set; }

    public int VencimentoPrestacaoId { get; set; }

    public virtual VencimentoPrestacao VencimentoPrestacao { get; set; }
}

MaturityPrestation

public class VencimentoPrestacao
{
    public int VencimentoPrestacaoId { get; set; }

    public DateTime? DataJaneiro { get; set; }
    public DateTime? DataFevereiro { get; set; }
    public DateTime? DataMarco { get; set; }
    public DateTime? DataAbril { get; set; }
    public DateTime? DataMaio { get; set; }
    public DateTime? DataJunho { get; set; }
    public DateTime? DataJulho { get; set; }
    public DateTime? DataAgosto { get; set; }
    public DateTime? DataSetembro { get; set; }
    public DateTime? DataOutubro { get; set; }
    public DateTime? DataNovembro { get; set; }
    public DateTime? DataDezembro { get; set; }
    public DateTime? DataCadastro { get; set; }
    public DateTime? DataTreze { get; set; }
    public DateTime? DataQuatorze { get; set; }
}
    
asked by anonymous 01.06.2015 / 15:02

2 answers

4

It's a simple spacing problem. If you want to use concatenation of String , always try to place a space between and statements. This piece:

"where c.AnoVigencia = @anoVigencia and" +
"c.FilialId = @filialId and" +
"c.TipoPrestacaoId = @prestacaoId" +
"and Ativo = 1";

Can be changed to:

" where c.AnoVigencia = @anoVigencia and " +
" c.FilialId = @filialId and " +
" c.TipoPrestacaoId = @prestacaoId " +
" and Ativo = 1 ";
    
01.06.2015 / 17:30
2

The only problem I see in your code is the separation of the code by + . Try to put inside a single "seletc" . And in the SplitOn , place the keys of your entities. It would look like this:

public IEnumerable<Cliente> BuscarClientePorAnoETipo(string ano, string filial, string tipoPrestacao)
        {

            var filialConvertInt = Convert.ToInt32(filial);
            var prestacaoConvertInt = Convert.ToInt32(tipoPrestacao);

            const string sql =
                @"select * from clientes c inner join TipoPrestacao t on (c.TipoPrestacaoId = t.TipoPrestacaoId)  
                inner join VencimentoPrestacoes v on (v.VencimentoPrestacaoId = t.VencimentoPrestacaoId)
                where c.AnoVigencia = @anoVigencia and c.FilialId = @filialId and c.TipoPrestacaoId = @prestacaoId and c.Ativo = 1";

            using (var cn = Connection)
            {
                cn.Open();

                var clientes = cn.Query<Cliente, TipoPrestacao, VencimentoPrestacao, Cliente>(sql, (c, t, v) =>
                {
                    c.TipoPrestacao = t;
                    c.TipoPrestacao.VencimentoPrestacao = v;
                    return c;
                }, new{anoVigencia = ano, FilialId = filialConvertInt, prestacaoId = prestacaoConvertInt}, splitOn: "ClienteId, TipoPrestacaoId, VencimentoPrestacaoId");
                            return clientes.ToList();
            }
        }

Any questions, the Dapper documentation has many usage examples.

    
01.06.2015 / 16:08