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

11

I'm using the Dapper which is a micro ORM used to map the objects according to the tables of the database following the relational model. So, I have some questions regarding mapping and class structure.

To illustrate the situation I created two classes that represent two tables of my database, the two classes are Cliente and Telefone , follow the code:

Client Class:

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

Phone 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), ie a customer can have multiple phone numbers.

Questions

  • This current class structure complies with Dapper for it to do relational mapping?
  • There are other structures that I can follow according to the scenario above?
  • How would a query with a inner join get the following fields Nome (Client) and Numero (Phone)?
asked by anonymous 27.01.2017 / 13:30

3 answers

5

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

You'll need to implement your own solution, or use a third-party library, such as AutoMapper .

So, my answers would be:

  

Does this structure conform to relational mapping?

Dapper can not provide relational mapping, just object properties.

  

Are there other frameworks that I can follow in the scenario above?

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

  

What would be 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 SQL clause containing its INNER JOIN .

    
27.01.2017 / 17:55
1

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

In my example you have Github source and everything else, but I'll explain here as well.

In the code below I'm looking for a bank account with various properties, account type, bank, customer, manager etc ... and that's the way I do the relationship. I hope I have 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;
}
    
03.11.2017 / 19:36
0

A way that keeps the code simpler to understand, since it does not 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 really knowledgeable about Dapper, but I do not think it's possible to create a one-to-many relationship without deploying a list of phones in the Cliente class.

Here's the result:

Edit:

AddingaTestProjectin.NetFiddle:

link

    
27.01.2017 / 17:43