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()
{
var query = "SELECT * from Books; Select * from Videos";
var results = dbConnection.QueryMultiple(query);
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)
{
var query = @"SELECT* from Organizations where id = @id;
Select * from Contacts where OrganizationId = @id";
var results = dbConnection.QueryMultiple(query, new { @id = id });
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";
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
- 18th September, 2018: First version