This explains a way to use Dapper to create a one-to-many query (as shown on this Dapper documentation page).
Note: For my money, this technique is a just a little fiddly and convoluted. I think a better to achieve the same kind of query results is an SQL view, Dapper, and a little LINQ.
There are two tables we need to query, the Artist table and the Song table. There is a many to one relationship between Artist and Song table; each Artist can have many Song rows.
Dapper CRUD models
Here are the C# models that correspond to the Artist and Song tables. These models are used with Dapper for the tables’ CRUD operations. Note that the base
artist CRUD model does not include a reference to an artist’s songs.
C# Artist model
C# Song model
The two simple queries below visualize some of the data:
Artist and Song query
songs with Dapper
The challenge is to create a query that shows each artist and its songs in a parent/child relationship, as shown below:
For the purposes of a Dapper join query, the C# model below extends the
Artist CRUD model with a
Song collection to store the artist’s songs. We’ll call this a query model–it doesn’t relate directly to a model in the database, rather it relates to a query.
The notion of extending a basic CRUD model for query (or other purposes) keeps the CRUD model clean for CRUD operations.
Although there is a
song CRUD class, it’s a little messy to reference the entire CRUD class as the
Songs property of the
ArtistQuery class. It’s actually not just messy, but it unnecessarily increases the size of the query payload, which is a consideration–especiallly for Ajax use with Json. Therefore, create a special-case small class that limits the properties to just those that you need. In this case, I used the
SongQuery class below.
The need for special-case classes (or records) for Dapper queriesis something that occurs frequently, especially with join queries. These “query model” class/record definitions should probably be parked away in their own namespace in production.
The Dapper join query uses the following SQL. Note this is a left join; it includes artists that do not have any songs.
The result set from the SQL above is shown below. Note the
ArtistID column below. In few minutes we’ll see how this column relates to Dapper’s
C# Dapper query code
The code below was derived from this Dapper one-to-many query.
JoinQuery method returns a
List<ArtistQuery> type. Its first statement declares a method-level
connection variable. A
using statement without braces means that the associated connection gets disposed when the method ends.
For this join, we want return a new
ArtistQuery for each artist and we want its
Songs property populated with a list of songs. Dapper needs some help getting that
Songs property populated. The variable
songDictionary caches each
ArtistQuery instance. It uses the instances stored here to accumulate the query’s songs (more on this in a moment).
sql variable declares the SQL that drives the Dapper join query.
Query method invokes the SQL query. In this case, the
Query method takes three generic arguments. The first two,
SongQuery are input classes (inferred from the SQL query) and the third is
ArtistQuery the query output class type. Dapper has
Query overloads that accept up to seven input classes (for up to a seven-way join).
Query method’s of use generics may not seem very intuitive–at least at first. For join queries, remember that the last class listed is the output type from the query. The
Query method’s first formal input parameter is the query’s SQL. In this case an SQL string is provided, but Dapper’s
Query method also works with stored procedures.
Query method’s second formal argument is a lamba with two parameters, an instance of
ArtistQuery and an instance of
SongQuery (which, again, are inferred from the SQL). This lambda utilmately returns an instance of
ArtistQuery, but there is a little logic in the lamba we need to discuss before we get to that.
Recall that given the SQL in use, a row of the result set looks like this:
While SQL doesn’t know or care about models being inferred from this query, Dapper does. Dapper knows that the columns in the blue box belong to the lambda’s incoming
artist model and that the columns in the red box belong to the lambda’s incoming
song model. (spoiler alert: we’ll see in a moment how the
splitOn parameter enables Dapper to divine which columns go to which model.) We’ll refer back to these blue and red boxes in a moment.
The lambda’s logic starts by declaring a nullable instance of
Next, the lambda checks to see if the incoming
artist instance has yet been cached in the
songDictionary. If it has, it populates the
out value with its properies.
If the incoming
artist instance has not yet been cached, the current
artist instance is assigned to the
artistQuery instance. Its
Songs property is then set to a new list of songs and the
artistQuery instance (which reflections the incoming
artist instance value, ie the blue box’s values) is added to the
songDictionary. These steps ensure that a place exists to start accumulating songs for a given artist.
If the current song instance isn’t null, the incoming song (the red box) is added to the artist’s list of songs. In this example the SQL uses a LEFT JOIN which collect all artists, not just those with songs. This test for null wouldn’t be necessary if the SQL was using an INNER JOIN (which fetches only artists with songs.)
Finally, the lambday returns the current
Remember our blue and red boxes? By default, Dapper splits the result set on the second occurence of an
Id column in the result set to infer model boundaries. However, if there isn’t an
Id column on which to split the model boundaries, use the
splitOn parameter to tell Dapper which column denotes model seperation for infering model boundaries from the result set.
If there are more than two models, the
splitOn value can be a comma-separated list of column names. In that case don’t put a space between the commas.
Because we want our data grouped by artist, it’s imperative to use Linq’s
Distinct method to specify the grouping. Linq’s
ToList ensures the
artistSongList is rendered as an
Query method finishes, the resulting
artistSongList is returned from our