Dapper SQL Join table into another

user_1402073 0
Viewed 179

Newbie here. I have the following 2 tables. One for Albums:

enter image description here

And the other for Songs:

enter image description here

So ALL of these songs belong to the "Freedom" album. In an api call I'm using dapper to display all the albums which is fine.

[HttpGet("[action]/{id}")]
        public async Task<IActionResult> GetSingleAlbumViaDapper(int id)
        {
            var sql = "SELECT * FROM Albums WHERE Id = @AlbumId";
            var album = (await _dbDapper.QueryAsync<Album>(sql, new { AlbumId = id })).SingleOrDefault();
        }

And here's the result:

enter image description here

But I want to join the Songs relevant for this album. I've tried the following but it's not working, any ideas why?

[HttpGet("[action]/{id}")]
        public async Task<IActionResult> GetSingleAlbumViaDapper(int id)
        {
            var sql = "SELECT * FROM Albums WHERE Id = @AlbumId JOIN Songs in Songs on Albums.Songs";
            var album = (await _dbDapper.QueryAsync<Album>(sql, new { AlbumId = id })).SingleOrDefault();
        }

All it says is: Incorrect syntax near the keyword 'JOIN'.

dapper sql-server sql c#
2 Answers

There is an error in how you're joining tables. First join, then filter (where-part)

The correct query would be something like that

SELECT * 
  FROM Albums a
  JOIN Songs s 
    ON s.AlbumId = a.Id
 WHERE a.Id = @AlbumId;

UPD. Since you're looking for songs only in the second query, from your table scema I see join is not needed here. You're already passing album.id to the query. Try this one and let me know the result

SELECT * 
  FROM Songs s 
 WHERE s.AlbumId = @AlbumId;
user_60336010

You need to use the join syntax for Dapper. I assume you have a Song class, I also assume your Album constructor creates the Songs list. Then you would do it as below, where you tell dapper to expect Albums and Songs and return an Album. The data should be split on the Id from Songs, that's what the splitOn parameter is for. This is typed from memory and with missing information, so it might require some adapting.

var sql = "SELECT * FROM Albums AS a INNER JOIN Songs AS s ON s.AlbumId = a.Id WHERE a.Id = @AlbumId";
Album foundAlbum = null;
_ = (await _dbDapper.QueryAsync<Album, Song, Album>(sql, (album, song) => 
    {
        if (foundAlbum is null)
        {
            foundAlbum = album;
        }
        foundAlbum.Songs.Add(song);
        return album;
    }, splitOn : "Id", new { AlbumId = id }));
// Use foundAlbum here....

The return value of the query isn't needed as the found album is in the foundAlbum variable. The query finds multiple rows, but only because there are multiple songs on the album.

user_55163390
Related