I am new to Dapper and trying to figure out how to query a Many-To-Many relationship.
I've looked around SO and Google but could not find an example.
I have a simple Many-To-Many scenario with 3 tables:
Albums table:
Artists table:
and the Many-To-Many table:
These are my POCOS:
public class Artist
{
public long Id { get; set; }
public string Name { get; set; }
}
public class Album
{
public long Id { get; set; }
public string Name { get; set; }
public List<Artist> Artists { get; set; }
}
Can someone provide the "correct" and efficient way to get a list of albums, and each album contains it's artists (the artists should have their Name property filled also) ?
I'm sure you must have found the solution by now. This could be helpful, may not be the neatest way to code it.
Is it possible to replicate Album and Artist classes? If so, this is what i would do.
public List<Artist> GetAll()
{
using (SqlConnection conn = new SqlConnection(Conn.String))
{
conn.Open();
using (var multi = conn.QueryMultiple(StoredProcs.Artists.GetAll, commandType: CommandType.StoredProcedure))
{
var artists = multi.Read<Artist, AlbumArtist, Artist>((artist, albumArtist) =>
{
artist.albumArtist = albumArtist;
return artist;
}).ToList();
var albums = multi.Read<Album, AlbumArtist, Album>(
(album, albumArtist, album) =>
{
album.albumArtist = album;
return albums;
}).ToList();
conn.Close();
return artists;
}
}
}
Here's how the proc would look like
CREATE PROCEDURE [dbo].[YourProcName]
AS
BEGIN
SET NOCOUNT ON;
SELECT * from Artist a
left join AlbumArtist aa
on a.ArtistId = aa.ArtistId;
EXEC dbo.Album_GetAll
WITH RESULT SETS UNDEFINED;
END
Hope this helps.