Dapper c# Inner join

c# dapper dapper-extensions

Question

Hi Im trying do that query in dapper, but the list contatos return with the values null for my class Client, i don't know what i doing wrong. my class Clt_cadCliente is one Client has several contact (clt_cadContatos). Help please.

var lookup = new Dictionary<int, Clt_cadCliente>();

            cn.Query<Clt_cadCliente, Clt_cadContatos, Clt_cadCliente>(@"
                            SELECT s.*, a.*
                            FROM Clt_cadCliente s
                            INNER JOIN Clt_cadContatos a ON s.IdCLiente = a.IdCliente  ", (s, a) =>
                            {
                                Clt_cadCliente shop;
                                if (!lookup.TryGetValue(s.IdCliente, out shop))
                                {
                                    lookup.Add(s.IdCliente, shop = s);
                                }
                                shop.Clt_cadContatos.Add(a);
                                return shop;
                            }, splitOn: "IdCliente").AsQueryable();

            var resultList = lookup.Values;

Class:

public partial class Clt_cadCliente
{
    public int IdCliente { get; set; }
    public Nullable<int> IdClientePai { get; set; }
    public string Codigo { get; set; }
    public string Nome { get; set; }
    public Nullable<System.DateTime> DataNasc { get; set; }
    public string Sexo { get; set; }
    public Nullable<int> IdEstCivil { get; set; }
    public string CPF { get; set; }
    public string RG { get; set; }
    public Nullable<System.DateTime> DataAdm { get; set; }
    public bool Pendencias { get; set; }
    public string DescPendencia { get; set; }
    public string Obs { get; set; }
    public string PessoaFJ { get; set; }
    public string NomeFantasia { get; set; }
    public string CodDep { get; set; }
    public string AtivoInativo { get; set; }
    public bool Ativado { get; set; }
    public Nullable<int> IdSitBloq { get; set; }
    public Nullable<int> SitBloq { get; set; }
    public string Profissao { get; set; }
    public string Empresa { get; set; }
    public string NomeEsposa { get; set; }
    public Nullable<System.DateTime> NascEsposa { get; set; }
    public Nullable<int> IdNaturezaPadrao { get; set; }
    public Nullable<int> ViaCarteirinha { get; set; }
    public Nullable<int> Casa { get; set; }
    public Nullable<int> Renda { get; set; }
    public Nullable<int> RendaComplementar { get; set; }
    public string Naturalidade { get; set; }
    public string Banco { get; set; }
    public string Agencia { get; set; }
    public string CidadeBanco { get; set; }
    public bool VeiculoProprio { get; set; }
    public Nullable<System.DateTime> DIB { get; set; }
    public string Foto { get; set; }
    public string Nbeneficio { get; set; }
    public Nullable<bool> LiberarExame { get; set; }
    public Nullable<System.DateTime> ValidadeExame { get; set; }
    public Nullable<int> EnviaBoleto { get; set; }
    public Nullable<int> IdUsuario { get; set; }
    public Nullable<long> IdClienteGlobal { get; set; }
    public virtual IList<Clt_cadContatos> Clt_cadContatos { get; set; }
}

Class 2:

public partial class Clt_cadContatos
{
    public int IdContato { get; set; }
    public string Nome { get; set; }
    public string Telefone { get; set; }
    public string Email { get; set; }
    public bool AdicionarLista { get; set; }
    public Nullable<int> IdCliente { get; set; }
}

Popular Answer

You are splitting on the wrong parameter. You need to split on IdContato.

The split on field tells Dapper where one entity ends and the next begins. If you select s.* followed by a.* you want to split into a second entity on the first field of the table a (I'm assuming that your sql tables resemble your classes.)




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why