0

I have 3 tables in Sql Server, one-to-one relationships.

The key is Indice in all 3 tables.

The 3 entities:

public class Client
{
     public int Indice { get; set; }
     public string Name { get; set; }
     public string Surname { get; set; }
     public string Company { get; set; }
     public string Tel1 { get; set; }
     public string Tel2 { get; set; }
}

public class CallClient
{
     public int Indice { get; set; }
     public string CallDateTime { get; set; }
     public string Status { get; set; }
}

public class ResponsePollClient
{
     public int Indice { get; set; }
     public string Question1 { get; set; }
     public string Question2 { get; set; }
     public string Question3 { get; set; }
     public string StatusPoll { get; set; }
}

I have this main entity

public class DataClient
{
     public Client client { get; set; }
     public CallClient callClient { get; set; }
     public ResponsePollClient pollClient { get; set; }
}

The SQL:

select c.Indice, .... , cc.Indice, ... , pc.Indice, ...
from Client c
inner join CallClient cc on c.Indice = cc.Indice
inner join PollClient pc on c.Indice = pc.Indice

How can I use Dapper for fill the List entity ?

Palle Due
  • 5,929
  • 4
  • 17
  • 32
Kiquenet
  • 14,494
  • 35
  • 148
  • 243

2 Answers2

1

The following code should map your pocos: 

   var sql = @"SELECT c.Indice 
             , c.Name
             , ....
             , cc.Indice
             , cc.CallDateTime 
             , ....
             , rpc.Indice 
             , rpc.Question1 
             , .... 
        FROM Client c
  INNER JOIN CallClient cc on c.Indice = cc.Indice
  INNER JOIN PollClient pc on c.Indice = pc.Indice";

        using (var conn = new SqlConnection())
        {
            var res = conn.Query<Client, CallClient, ResponsePollClient, DataClient>(sql, (c, cc, pc) =>
            {
                   var d = new DataClient() 
                   {
                     client = c, 
                     callClient = cc, 
                     pollClient = pc
                    };

                return dc;
            }, splitOn: "Indice");
        }
Alex
  • 7,901
  • 1
  • 41
  • 56
1

The code should look something like this:

var entityList = connection.Query<DataClient, Client, CallClient,ResponsePollClient,DataClient>(
            sql,
            (dataClient, client, callClient, responsePollClient) =>
            {
                dataClient.client = client;
                dataClient.callClient = callClient;
                dataClient.pollClient = responsePollClient;
                return dataClient;
            },
            splitOn: "Indice").ToList();

It is pretty straightforward, you can find the complete documentation here

SilentRage47
  • 934
  • 2
  • 14
  • 31
  • `splitOn: "Indice,Indice,Indice"` **vs** `splitOn: "Indice"` ? – Kiquenet Apr 23 '20 at 10:09
  • A single "Indice" should suffice. https://github.com/StackExchange/Dapper/blob/a18dc63c688b5173c5dde8b1243b74e26fa262c4/Dapper/SqlMapper.cs#L1572 – Alex Apr 23 '20 at 11:00
  • Error: `ArgumentException: When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id` – Kiquenet Apr 24 '20 at 09:32
  • Try it by providing all split on columns. "Indice,Indice,Indice" – Alex Apr 24 '20 at 10:41