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; } |