Tutorial on Handling Multiple Resultsets and Multiple Mapping using Dapper

• Download source code - 1 MB

Introduction

In this article, we will look at how to use dapper to read multiple resultsets from 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 once 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 handle 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 the 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 multiple queries separated 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 purposes 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, we can leverage the QueryMultiple to do this. This is how the relationship looks in the database.

First let's check 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; }
}

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 multiple queries separated 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 straight forward as they required us to write 2 separate queries and then gather the result 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 feature 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 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.

Now 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 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 that 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 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.

History

• 18^th September, 2018: First version