How to map 1:N (one to many) in Dapper?

Question

I’m using the Dapper which is a micro ORM used to map objects according to the database tables following the relational model. Therefore, I have some doubts regarding the mapping and the structure of the classes.

To illustrate the situation I created two classes representing two tables in my database, the two classes are Cliente and Telefone, follows the code:

Client class:

public class Cliente
{
    int IdCliente {get; set;}
    string Nome {get; set;}    
}

Telephone class:

public class Telefone 
{
    int IdTelefone {get; set;}
    int IdCliente {get; set;}
    string Numero {get; set;}
}

The relationship of these two classes should represent a 1:N relationship (one for many), i.e., a customer may have multiple phone numbers.

Doubts

• This current class structure conforms to Dapper for him to do the relational mapping?
• There are other structures that I can follow according to the scenario above?
• What would an appointment with a inner join to obtain the following fields Nome (Client) and Numero (Telephone)?

Answer 1

Dapper is an extremely performative tool for working at a level very close to ADO interfaces. However, no interface is provided to express relationships.

You will need to implement your own solution, or use a third-party library such as Automapper.

So my answers would be:

This structure is in compliance for it to do relational mapping?

Dapper cannot provide relational mapping, only of object properties.

There are other structures that I can follow according to the above scenario?

No. Or, better described, there are no requirements in the definition expected by Dapper to declare structure dependencies.

How would a query with an Inner Join that obtained the following fields Name (Client) and Number (Phone)?

You can simply implement a class with these two properties to contain the result of a clause SQL containing your INNER JOIN.

Answer 2

I just published an article on multi-mapping with Dapper Multi-mapping with Dapper

In my example have Github source and everything, but I will explain here too.

In the code below I am looking for a bank account with various properties, type of account, bank, client, manager etc... and this is how I do the relationship. I hope I’ve helped.

public static List<BankAccount> GetList()
{
    string sqlQuery = @"SELECT
    BA.Id, BA.Account, BA.Branch,
    U.Id, U.Name,
    A.Id, A.Street, A.City,
    C.Id, C.Name,
    BK.Id, BK.Name,
    ACT.Id, ACT.Name,
    ACC.Id, ACC.Name,
    M.Id, M.Name
    from BankAccount BA
    inner join [User] U on U.Id = BA.UserId
    inner join [Address] A on A.Id = U.AddressId
    inner join Currency C on C.Id = BA.CurrencyId
    inner join Bank BK on BK.Id = BA.BankId
    inner join AccountType ACT on ACT.Id = BA.AccountTypeId
    inner join AccountCategory ACC on ACC.Id = BA.AccountCategory
    inner join Manager M on M.Id = BA.ManagerId";

    List<BankAccount> ret;
    using (var db = new SqlConnection(connstring))
    {
        ret = db.Query<BankAccount>(sqlQuery,
            new[]
            {
                typeof(BankAccount),
                typeof(User),
                typeof(Address),
                typeof(Currency),
                typeof(Bank),
                typeof(AccountType),
                typeof(AccountCategory),
                typeof(Manager)
            },
            objects =>
            {
                var bankaccount = objects[0] as BankAccount;
                var user = objects[1] as User;
                var address = objects[2] as Address;
                var currency = objects[3] as Currency;
                var bank = objects[4] as Bank;
                var accounttype = objects[5] as AccountType;
                var accountcategory = objects[6] as AccountCategory;
                var manager = objects[7] as Manager;

                bankaccount.User = user;
                bankaccount.User.Address = address;
                bankaccount.Bank = bank;
                bankaccount.Currency = currency;
                bankaccount.AccountType = accounttype;
                bankaccount.AccountCategory = accountcategory;
                bankaccount.Manager = manager;

                return bankaccount;
            }, splitOn: "Id, Id, Id, Id, Id, Id, Id, Id").AsList();
    }

    return ret;
}

Answer 3

One way that keeps the code simpler to understand, because it doesn’t use mapping, is this, using dynamic:

using Dapper;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace TesteDapper
{
    class Program
    {
        static void Main(string[] args)
        {
            var conexao = @"Server=.\sqlexpress;Database=StackOverFlow;Trusted_Connection=True;";

            using (var conn = new SqlConnection(conexao))
            {
                conn.Open();
                var sql = @"SELECT * 
                             FROM CLIENTE CLI
                             LEFT JOIN TELEFONE TEL 
                               ON TEL.IdCliente = CLI.IdCliente";

                dynamic resultList = conn.Query(sql);
            }
        }
    }

    public class Cliente
    {
        public int IdCliente { get; set; }
        public string Nome { get; set; }
        public List Telefones { get; set; }
    }

    public class Telefone
    {
        public int IdTelefone { get; set; }
        public int IdCliente { get; set; }
        public string Numero { get; set; }
    }
}

I’m not very knowledgeable about Dapper, but I don’t think it’s possible to create a one-to-Many relationship without implementing a phone list in class Cliente.

It follows result:

Edit:

Adding a test project to . Net Fiddle:

https://dotnetfiddle.net/JBl8oo