Dapper with Mapping by code: Multi-Mapping with repeating column names

Question

I'm trying to perform a simple query and the result data is almost all null.

I have this table structure

Table Registros

ID            |  Autonumeric
TareaM_Id     |  Numeric
Fecha         |  Date/Time

and Macro_tareas table

ID            |  Autonumeric
Nombre        |  Short Text

I have mapped the classes in C# like this:

[Table("Registros")]
public class Registro
{
    [Column("ID")]
    public virtual int ID { get; set; }

    [Column("Fecha")]
    public virtual DateTime Fecha { get; set; }

    [Column("TareaM_Id")]
    public virtual int TareaM_Id { get; set; }

    public virtual MacroTarea MacroT { get; set; }
}

[Table("Macro_tarea")]
public class MacroTarea
{
    [Column("ID")]
    public virtual int ID { get; set; }

    [Column("Nombre")]
    public virtual string Nombre{ get; set; }

    public virtual ICollection<Registro> Registros { get; set; }

}

This is the query i'm trying to use

string sql = @"SELECT reg.ID, mac.ID 
    FROM Registros as reg INNER JOIN Macro_tarea as mac on reg.TareaM_Id = mac.ID
    WHERE Fecha = @Fecha";

using (IDbConnection db = new OleDbConnection(ConnectionString))
{
    var result = db.Query<Registro,MacroTarea, Registro>(sql, 
    (reg,mac) =>
    {
        reg.MacroTarea = mac;
        return reg;
    }
    ,new { @Fecha = new DateTime(2019, 1, 4).Date }
    , splitOn: "mac.ID")
    .AsList();                                            
}

I'm trying to only retrieve ids, but both id become null why is this happening?

The thing is, if I add Registros.Fecha and Macro_tarea.Nombre to the query, it got the value correctly. But id keep coming null.

Apparently the issue is happening only with ids. I suspect this issue is due to duplicate column names.

I'm working with Microsoft Access just in cast that matters.

My question is not similar to the possible duplicate because I have the classes defined as they should be mapped.

Answer 1

As we discussed in comments, this is an issue due to duplicate column names in two tables. This is where the similar issue and solution could be found. But, it does not include "mapping by code" as you said. So it is not exact duplicate.

I suggest you change the names of ID fields in your tables to avoid colliding them. Of-course, you should also change the name of your POCO properties and mappings accordingly.

If you cannot change the column names in table, change the POCO property name, and use the column alias in SQL query to match those new property names.

I hope this helps you.