Convert Linq query to Dapper

Question

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 am modifying my selects, using Dapper in all my appointments.

Because I don’t know the tool very well, I’m having trouble converting the query to the Dapper. Based on what I read in some articles, I came to this consultation:

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 executing, returns me the following error:

Details of the Exception:

System.Data.Sqlclient.Sqlexception: Incorrect syntax near 'andc'.

Error of Origin:

Line 32: cn. Open();

Line 33:

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

Line 35: {

Row 36: c.Type station = t;

I believe it is the error of syntax, but I haven’t found anything that can help me. When executing this code on SQL Server, it returns me the correct values.

My entities are as follows:

Client

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; }
}

Typoprestacao

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; }
}

Expiration date

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; }
}

Answer 1

It is a simple spacing problem. If using concatenation of String, always try to put a gap between sentences and. 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 ";

Answer 2

The only problem I see in your code is separation of the code by + . Try to put inside a single "seletc". And in the Spliton, put the keys to your entities. That would be your code:

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 doubts, the documentation of the Dapper has many examples of use.