Lorenzo Belfanti Lorenzo Belfanti - 7 months ago 5
SQL Question

Some value return null on Dapper Multi Mapping

I'm having a problem using

Dapper
.
I have a list of the
Rubrica
class that contains the field
valore
.
When I run a query with a JOIN and identify the agenda type, the
valore
field remains set to
null


My two classes
Rubrica
and
TipoAgenda


public class Rubrica // Same as table anagrafico_rubrica
{
public int id_rubrica { get; set; }
public string cod_anagrafica { get; set; }
public string descrizione_contatto { get; set; }
public TipoRubrica tipo { get; set; }
public string valore { get; set; }
}

public class TipoRubrica // Same as table anagrafico_tipo_rubrica
{
public int id_tipo_rubrica { get; set; }
public string descrizione_tipo_rubrica { get; set; }
}


I created a function that returns me a list of
Agenda
doing a JOIN with table
anagrafico_tipo_rubrica


public List<Rubrica> GetAgendaAnagrafico(string codiceAnagrafico)
{
using (DatabaseConnection db = new DatabaseConnection())
{
const string query = @"SELECT * FROM anagrafico_rubrica JOIN anagrafico_tipo_rubrica ON tipo = id_tipo_rubrica WHERE cod_anagrafica = @anagrafico";
var parametri = new { anagrafico = codiceAnagrafico };
return db.con.Query<Rubrica, TipoRubrica, Rubrica>(query, (rubrica, tipo) => { rubrica.tipo = tipo; return rubrica; }, parametri, splitOn: "tipo").ToList();
}
}


Here you can see what the query returns

My result Query

And here you see how int the
Agenda
list there is the value of
valore
set to
null


'valore' have value null

Jcl Jcl
Answer

You are splitting on tipo, which comes before valore in your query, so dapper is splitting the columns and thinking valore is for TipoRubrica instead of for Rubrica

Select the order of the fields explictly on your query

SELECT id_rubrica, 
       cod_anagrafica, 
       descrizione_contatto, 
       valore, 
       tipo,       // <-- you are splitting here. columns above are for 
                   //     first type, columns below for second
       id_tipo_rubrica, 
       descrizione_tipo_rubrica 
    FROM ...

So when you split on tipo, valore is before that, and it's mapped to the first type (Rubrica), instead of to the second (TipoRubrica)

Comments