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 database in a single database call. We will look at the scenarios where we might want to do this and how dapper let 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 mutliple 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, lets 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 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 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, QueryMultipleand Readmethods. let’s now turn our focus to how we can actually perform these operations in code.

Query unrelated entities

lets 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:

With these models in place, lets see how we can use dapper to retrieve these results using only one database call:

Now lets 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 exist a one to many relationship between entities. Lets try to visualize this using an example of Organizationand Contacts. An Organizationwill typically have multiple Contactsassociated with it. If we want to retrieve an organization and want to retrieve all the associated contacts along with, we can leverage the QueryMultipleto do this. This is how the relationship looks in the database.

First lets 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 Organizationhas a list of Contacts.

Now lets 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 QueryMultiplemethod.

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 Organizationobject. The second query’s result got pushed as the Contactcollection of the same Organizationobject.

Now lets 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 quire 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. Lets 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.

Lets take an example of Contactand Passport. Each Contactcan only have one passport. Lets 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 lets see how our entities look for Contactand Passport.

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

In the above code we are using the Querymethod’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 Contactand Passportand then expect the result back in an object of type Contact.

Now, lets 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 Contactand Passport types and assigning the Passportproperty of Contactas the Passportvalue 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 splitOnis 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 PassportIdcolumn is found, all the columns will be mapped to the first type i.e. Contactand 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 needs to be mapped, the splitOnwill 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 lets 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.