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.
The artist table
|
1 2 3 4 5 6 7 8 |
CREATE TABLE [dbo].[Artist] ( [Id] INT NOT NULL PRIMARY KEY IDENTITY, [Name] NVARCHAR(100) NOT NULL, [PrefixWithThe] bit NOT NULL DEFAULT 0, [Added] datetime, [Updated] datetime ) |
The song table
|
1 2 3 4 5 6 7 8 |
CREATE TABLE [dbo].[Song] ( [Id] INT NOT NULL PRIMARY KEY IDENTITY, [ArtistId] INT FOREIGN KEY REFERENCES [dbo].[Artist](Id), [Title] NVARCHAR(200) NOT NULL, [Added] datetime, [Updated] datetime ) |
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
|
1 2 3 4 5 6 7 8 |
public record Artist { public int Id { get; set; } public string Name { get; set; } public bool PrefixWithThe { get; set; } public DateTime? Added { get; set; } public DateTime? Updated { get; set; } } |
C# Song model
|
1 2 3 4 5 6 7 8 |
public record Song { public int Id { get; set; } public int ArtistId { get; set; } public string Title { get; set; } public DateTime? Added { get; set; } public DateTime? Updated { get; set; } } |
The two simple queries below visualize some of the data:
Artist and Song query
Joining artist and 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.
|
1 2 3 4 |
public class ArtistQuery : Artist { public ICollection<SongQuery>? Songs { get; set; } } |
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.
|
1 2 3 4 |
public class SongQuery { public string Title { get; set; } = default!; } |
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.
|
1 2 3 4 |
SELECT a.*, s.* from [dbo].[artist] a LEFT JOIN [dbo].[song] s ON a.Id = s.ArtistId ORDER BY a.Name |
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 splitOn parameter.
C# Dapper query code
The code below was derived from this Dapper one-to-many query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
public List<ArtistQuery> JoinQuery() { using var connection = new SqlConnection(CONNECTION_STRING); var songDictionary = new Dictionary<int, ArtistQuery>(); string sql = @" SELECT a.Id, a.Name, a.PrefixWithThe, s.ArtistId, s.Title from [dbo].[artist] a LEFT JOIN [dbo].[song] s ON a.Id = s.ArtistId ORDER BY a.Name "; var artistSongList = connection.Query<ArtistQuery, SongQuery, ArtistQuery>( sql, (artist, song) => { ArtistQuery? artistQuery; if (!songDictionary.TryGetValue(artist.Id, out artistQuery)) { artistQuery = artist; artistQuery.Songs = new List<SongQuery>(); songDictionary.Add(artist.Id, artistQuery); } if (song != null) { artistQuery.Songs.Add(song); } return artistQuery; }, splitOn: "Title") .Distinct() .ToList(); return artistSongList; } |
Annotated code
The 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.
|
1 2 3 |
public List<ArtistQuery> JoinQuery() { using var connection = new SqlConnection(CONNECTION_STRING); |
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).
|
1 |
var songDictionary = new Dictionary<int, ArtistQuery>(); |
The sql variable declares the SQL that drives the Dapper join query.
|
1 2 3 4 5 6 7 |
string sql = @" SELECT a.Id, a.Name, a.PrefixWithThe, s.ArtistId, s.Title from [dbo].[artist] a LEFT JOIN [dbo].[song] s ON a.Id = s.ArtistId ORDER BY a.Name "; |
Dappy’s Query method invokes the SQL query. In this case, the Query method takes three generic arguments. The first two, ArtistQuery and 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).
The 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.
|
1 2 |
var artistSongList = connection.Query<ArtistQuery, SongQuery, ArtistQuery>( sql, |
The 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.
|
1 2 |
(artist, song) => { |
The lambda’s logic starts by declaring a nullable instance of ArtistQuery.
|
1 |
ArtistQuery? artistQuery; |
Next, the lambda checks to see if the incoming artist instance has yet been cached in the songDictionary. If it has, it populates the artistQuery out value with its properies.
|
1 |
if (!songDictionary.TryGetValue(artist.Id, out artistQuery)) |
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.
|
1 2 3 4 5 |
{ artistQuery = artist; artistQuery.Songs = new List<SongQuery>(); songDictionary.Add(artist.Id, artistQuery); } |
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.)
|
1 2 3 4 |
if (song != null) { artistQuery.Songs.Add(song); } |
Finally, the lambday returns the current artistQuery instance.
|
1 |
return artistQuery; |
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 Query method’s 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.
|
1 |
}, splitOn: "ArtistId") |
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 IEnumerable value.
|
1 2 |
.Distinct() .ToList(); |
When the Query method finishes, the resulting artistSongList is returned from our JoinQuery function.
|
1 2 |
return artistSongList; } |
