Building objects with many-to-many relationship using Dapper

Question

Consider an Sqlite database, whose partial schema is shown below (we are not considering the Book_Tag table here). Note the many-to-many relationship between media items and tags using the link table Media_Tag:

An object model for these tables is as follows:

public enum MediaType
{
    Dvd,
    BluRay,
    Cd,
    Vhs,
    Vinyl,
    Other
}

public class MediaItem 
{
    public MediaType type { get; set; }
    public long number { get; set; } 
    public int runningTime { get; set; }
    public int releaseYear { get; set; }

    public ICollection<Tag> tags { get; set; }
}

public class Tag 
{
    public string name { get; set; }
}

currently, Dapper is being used to read from the Media table, but without considering tags. The code is as follows:

public IEnumerable<MediaItem> readAll()
{
    using (var db = new SqliteConnection(this.connectionString))
    {
        db.Open();

        var sql = "SELECT * FROM Media;";
        return db.Query<MediaItem>(sql);
    }
}

public MediaItem readById(int id)
{
    using (var db = new SqliteConnection(this.connectionString))
    {
        db.Open();

        var sql = "SELECT * FROM Media WHERE id = @id;";
        var @params = new { id = id };
        return db.Query<MediaItem>(sql, @params).First();
    }
}

How to change this so that the tag property of MediaItem is considered when creating the objects, for both cases (read by id and read all rows from the table)? Is a join query required? I'm sure Dapper has a way of doing this nicely, but I don't know how it's done.

Answer 1

You are not interested in anything from the link table so something like this SQL should do:

SELECT M.Id, M.title, M.type, M.Number, M.image, M.runningTime, M.releaseYear, T.Id, T.Name FROM Media as M 
INNER JOIN Media_Tag AS MT ON M.id = MT.mediaId
INNER JOIN Tags AS T ON T.id = MT.tagId

If SqLite allows you can use M.*, T.* instead.

I have taken the liberty to add Id properties to your entity classes. I think you are going to need it, otherwise all your tags will be different instead of being unique. You might make it work without it, but it should make your life easier.

public class MediaItem 
{
    public int Id { get; set; } // New
    public MediaType type { get; set; }
    public long number { get; set; } 
    public int runningTime { get; set; }
    public int releaseYear { get; set; }

    public ICollection<Tag> tags { get; set; }
}

public class Tag 
{
    public int Id { get; set; } // New
    public string name { get; set; }
}

Since both your entity classes have a unique id, you will have to pick them up and make sure they are unique going through the results. We do that by using dictionaries to keep them. I'm only showing the ReadAll, you should be able to do ReadById accordingly.

string sql = "<see above>";

using (var db = new SqliteConnection(this.connectionString))
{            
    var mediaDictionary = new Dictionary<int, Media>();
    var tagDictionary = new Dictionary<int, Tag>();

    var list = db.Query<Media, Tag, Media>(
    sql,
    (media, tag) =>
    {
        Media mediaEntry;

        if (!mediaDictionary.TryGetValue(media.Id, out mediaEntry))
        {
            // Haven't seen that one before, let's add it to the dictionary
            mediaEntry = media;
            mediaDictionary.Add(mediaEntry.Id, mediaEntry);
        }

        Tag tagEntry;

        if (!tagDictionary.TryGetValue(tag.Id, out tagEntry))
        {
            // Haven't seen that one before, let's add it to the dictionary
            tagEntry = tag;
            tagDictionary.Add(tagEntry.Id, tagEntry);
        }

        // Add the tag to the collection
        mediaEntry.Tags.Add(tagEntry);
        return mediaEntry;
    },
    splitOn: "Id") // This default and could be omitted
    .Distinct()
    .ToList();