Dapper - What is the most efficient way to query Many-To-Many relationship?

Question

Dapper - What is the most efficient way to query Many-To-Many relationship?

dapper many-to-many sql-server

Question

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:

Albums

Artists table:

Artists

and the Many-To-Many table:

AlbumArtist

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) ?

Yaron Levi

Popular Answer

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.

Salman Syed

View more on Stack Overflow

Dapper - What is the most efficient way to query Many-To-Many relationship?

Question

Popular Answer

Performance

Expression Evaluator