Tutorial on Handling Multiple ResultSets and Multiple Mapping Using Dapper

In this article, we will look at how to use Dapper to read multiple ResultSets from the database in a single database call. We will look at the scenarios where we might want to do this and how Dapper lets us achieve this with its Query and QueryMultiple methods.

Background

When we are talking about data-centric applications, there could be scenarios where we might want to retrieve multiple results from the database. The multiple ResultSets could either be related or unrelated. To do this, rather than making multiple round trips to the database, we can actually use Dapper to retrieve the results in one database call itself and then map the results to the desired objects in our code.

Before we go ahead and start looking at how to do this, let's first try to understand the scenarios where we might want to do this in our application:

• Query unrelated entities: The entities being requested are not at all related to each other
• Query related entities having 1 to many relationship: The entities being requested have 1-* relationship and we need to Handling multiple ResultSets in our code
• Query related entities having 1 to 1 relationship: The entities being requested have 1-1 relationship and we need perform Handling multiple Mapping in our code

In the first scenario, we have totally unrelated entities, so essentially, we just want to execute two independent queries to retrieve the data and then map it to these entities. In the second scenario, the entities being returned are related as 1-*,  so we want to retrieve the data and then map the results into the POCOs having 1 to many relationship. And finally, in the third scenario, the entities being returned are related as 1-1, so we want to retrieve the data and then map the results into the POCOs having 1 to 1 relationship.

Let's look at some code now to understand how all this can be achieved using Dapper.

Note: We will be building on top of our existing project that we built in our previous Dapper article. It is highly advisable to read the first article before reading this one: Absolute Beginner's Tutorial on understanding and using Dapper ORM[^]

Using the Code

All this can be archived by using Dapper’s Query, QueryMultiple, and Read methods. let’s now turn our focus to how we can actually perform these operations in code.

Query Unrelated Entities

Let's say we want to retrieve the list of books and videos from an API. We can do this by having two simple select all queries and the database result will look something like following:

Now, to be able to do the same from our code, we first need to define our entities:

public class Book
{
public int ID { get; set; }
public string BookName { get; set;}
public string ISBN { get; set; }
}

public class Video
{
public int ID { get; set; }
public string VideoName { get; set; }
}

With these models in place, let's see how we can use Dapper to retrieve these results using only one database call:

public IActionResult Index()
{
// define our SQL query - it contains mulitple queries seprated by ;
var query = "SELECT * from Books; Select * from Videos";

// Execute the query
var results = dbConnection.QueryMultiple(query);

// retrieve the results into the respective models
var books = results.Read<Book>();
var videos = results.Read<Video>();

return Ok(new { Books = books, Videos = videos});
}

Now let's run this in POSTMAN to see the results in action:

Note: I have created a simple API controller to test this code, all the DB access code is baked inside. It is only for demonstration purpose and in real-world applications, such code should not be used at all.

Query Related Entities Having 1 to Many Relationship

Another typical scenario for retrieving the related entities will be where there exists a one to many relationship between entities. Let's try to visualize this using an example of  Organization  and  Contacts  . An  Organization   will typically have multiple  Contacts  associated with it. If we want to retrieve an organization and want to retrieve all the associated contacts along with it, we can leverage the  QueryMultiple  to do this. This is how the relationship looks in the database.

First, let's see how we will do the same using SQL query.

Now, if we have to do the same thing in our code, we first need to define our entities. Notice that our entities will also model the one to many relationship in a way that each Organization  has a list of  Contacts .

public class Organization
{
public int ID { get; set; }
public string OrganizationName { get; set; }

public List<contact> Contacts { get; set; }
}

public class Contact
{
public int ID { get; set; }
public int OrganizationId { get; set; }
public string ContactName { get; set; }
}
</contact>

Now let's look at the code that we can use to retrieve these related entities and see how we can populate entities related with 1 to many relations with Dapper’s QueryMultiple  method.

[HttpGet("{id}")]
public IActionResult GetOrganization(int id)
{
// define our SQL query - it contains mulitple queries seprated by ;
var query = @"SELECT* from Organizations where id = @id;
Select * from Contacts where OrganizationId = @id";

// Execute the query
var results = dbConnection.QueryMultiple(query, new { @id = id });

// retrieve the results into the respective models
var org = results.ReadSingle<Organization>();
org.Contacts = results.Read<Contact>().ToList();

return Ok(org);
}

In the above code, we can see how we executed 2 queries at the same time. We took the first query’s results and populated our Organization  object. The second query’s result got pushed as the Contact  collection of the same Organization  object.

Now let's run this in POSTMAN to see the results in action:

Query Related Entities Having 1 to 1 Relationship

The first 2 scenarios were fairly straightforward as they required us to write 2 separate queries and then gather the results from each query independently to create our model objects as required.

But the scenario of having 1 to 1 relationship is quite tricky. As from the database perspective, we can retrieve the related entities in a single SQL query itself but then we want to map the single ResultSet into multiple objects in our code. This can be done using the multiple mapping features available in Dapper. Let's try to understand this with the help of an example.

Note: We can still use the same method that we used in a 1 to many relationship to retrieve the data related as 1 to 1, but this section shows how that can be done using single SQL and mapping the results.

Let's take an example of Contact  and Passport  . Each Contact  can only have one  passport . Let's try to visualize this database relationship first.

Now let's see if we need to retrieve the list of contacts and their passport information from the database, how can we do that in SQL.

Let's see how our entities look for Contact  and Passport  .

public class Contact
{
public int ID { get; set; }
public int OrganizationId { get; set; }
public string ContactName { get; set; }

public Passport Passport { get; set; }
}

public class Passport
{
public int ID { get; set; }
public int Contactid { get; set; }
public string PassportNumber { get; set; }
}

Now let's see how we can retrieve these related entities from the database and use Dapper multiple mapping to populate our POCOs with the same relationship intact.

[HttpGet("{id}")]
public IActionResult GetContact(int id)
{
var query = @"Select
c.ID, c.Organizationid, c.ContactName,
p.ID as PassPortId, p.ContactId, p.PassportNumber
from Contacts c,
Passports p
where c.ID = p.ContactID
and c.id = @id";

// Execute the query
var contact = dbConnection.Query<Contact, Passport, Contact>(query, MapResults, new { @id = id }, splitOn: "PassportId");

return Ok(contact);
}

private Contact MapResults(Contact contact, Passport passport)
{
contact.Passport = passport;
return contact;
}

In the above code, we are using the Query  method’s overloaded version that is taking multiple types. The types that are being passed are the type parameter for each object that we want to map and the last type parameter is the additional parameter representing the type of object this query will return.

So in our query, we want to map the result to type Contact and Passport and then expect the result back in an object of type Contact.

Now, let's look at the actual arguments being passed in the query method.

• The first argument is the SQL query itself.
• The second argument is the mapping function, which will take the results, bind it to respective types, and then create the required return type and return that return type. In our code, it is taking Contact and Passport types and assigning the Passport property of Contact as the Passport value being passed. Once this is done, the resultant Contact type is returned back.
• The third argument is the command parameter @id.
• And the last parameter, splitOn, is the column name that will tell the Dapper what columns must be mapped to the next object. In our example, we are passing this value as PassportId, which means that until the PassportId column is found, all the columns will be mapped to the first type i.e. Contact, and then, the columns after that will be mapped to the next parameter type i.e. Passport .

Note: In case we have more than 2 objects that need to be mapped, the splitOn will be a comma separated list where each column name will act as the delimiter and the start of the mapping columns for the next object type.

Now let's run this in POSTMAN to see the results in action:

And there we have it. We are using Dapper to retrieve multiple ResultSets from database to avoid database round trips.

Point of Interest

In this article, we looked at how we can avoid multiple database round trips by using the features provided by Dapper to retrieve multiple related or unrelated entities in just one go. This has been written from a beginner's perspective. I hope this has been somewhat informative.

Download the sample code for this article here: DapperTest