Mult Mapping Dapper

Question

I am having problems with Mult Mapping of Dapper, when I try to execute the following query is not mapping the object ids select is this:

var sql = @" SELECT 
                    ofi.id_oficina,
                    ofi.codigo,
                    p.id_pedido, 
                    p.data_atuacao_oficina ,
                    p.numero, 
                    s.id_sinistro,
                    s.codigo,
                    v.id_veiculo,
                    v.placa, 
                    v.modelo, 
                    v.tipo ,
                    m.id_marca,
                    m.nome
                FROM 
                    pecas.oficinas ofi
                    INNER JOIN pecas.pedidos p
                        ON p.cod_oficina = ofi.id_oficina
                    INNER JOIN pecas.sinistros s
                        ON s.id_sinistro = p.cod_sinistro
                    INNER JOIN pecas.veiculos v
                        ON v.id_veiculo = p.cod_veiculo
                    INNER JOIN pecas.marcas m
                        ON m.id_marca = v.cod_marca
                    INNER JOIN pecas.clientes c
                        ON c.id_cliente = s.cod_cliente
                    INNER JOIN pecas.itenspedido ip
                        ON ip.cod_pedido = p.id_pedido
                        AND ip.autorizacao_cod_itempedido_fornecedor IS NOT NULL
                    INNER JOIN pecas.itenspedido_fornecedores ipf
                        ON ipf.id_itempedido_fornecedor = ip.autorizacao_cod_itempedido_fornecedor
                        AND ipf.devol_data_autorizacao IS NULL
                        AND ipf.data_prazo_entrega IS NOT NULL
                    INNER JOIN pecas.pedidos_fornecedores pf 
                        ON pf.cod_pedido = p.id_pedido
                        AND pf.data_autorizacao IS NOT NULL
                    LEFT JOIN conciliacao.conciliacoes conc 
                        ON conc.id_conciliacao = pf.cod_conciliacao
                WHERE 
                    (p.logistica_data_encerramento IS NULL OR p.logistica_data_encerramento >= current_date-60)
                    AND s.cod_cliente = @idCliente
                    AND p.cancelado IS NOT TRUE
                    AND ofi.email IS NOT NULL
                    AND (v.placa ILIKE @placa OR s.codigo ILIKE @sinistro)";

            var pedidos = Conn.Query<Oficina, Pedido, Sinistro, Veiculo, Marca, Pedido>
            (sql, (oficina, pedido, sin, veiculo, marca) =>
                {
                    pedido.Sinistro = sin;
                    veiculo.Marca = marca;
                    pedido.Veiculo = veiculo;
                    pedido.Oficina = oficina;


                    return pedido;
                },
            new
            {
                idCliente = codCliente,
                placa = string.IsNullOrEmpty(placa) ? "" : placa,
                sinistro = string.IsNullOrEmpty(sinistro) ? "" : sinistro
            }, splitOn: "id_pedido,id_sinistro,id_veiculo,id_marca")

I tried to do by selecting all fields ofi.*,p.*,s.*,v.*,m.* and with the fields I will need (I prefer to select only with the fields I will use)

Obviously I need the Ids to know who is who, when I select the object by ID it maps correctly (not the automapper)

EDIT: My question was marked as duplicate but I have no error popping, the query returns results but the results are not filled in the list of objects.

Follow 2 prints of the error

Answer 1

The problem is that the name of the Field in the database (your select) is different from the name of your Entity.

Just change it to be equal and everything will be solved. A simple option is to use Alias in your query, for example:

SELECT
       ofi.id_oficina as IdOficina,

Another option would be to manually map the dynamic object of the query with the specific data, for example:

var sql = @"select top 1 person_id, first_name, last_name from Person";
using (var conn = ConnectionFactory.GetConnection())
{
    var person = conn.Query<Person>(sql)
                     .Select(item => new Person()
                     {
                       PersonId = item.person_id,
                       FirstName = item.first_name,
                       LastName = item.last_name
                     }
                     .ToList();

    return person;
}

Note that I am speaking explicitly that Personid = item.person_id.

Some other details, including how to use ColumnAttribute, you can check in the answers of this question.