When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id", "splitOn

dapper

Question

I'm trying to use the Multi-mapping feature of dapper to return a list of Album and associated Artist and Genre.

public class Artist
{
public virtual int ArtistId { get; set; }
public virtual string Name { get; set; }
}    


public class Genre
{
public virtual int GenreId { get; set; }
public virtual string Name { get; set; }
public virtual string Description { get; set; }
}


public class Album
{
public virtual int AlbumId { get; set; }
public virtual int GenreId { get; set; }
public virtual int ArtistId { get; set; }
public virtual string Title { get; set; }
public virtual decimal Price { get; set; }
public virtual string AlbumArtUrl { get; set; }
public virtual Genre Genre { get; set; }
public virtual Artist Artist { get; set; }
}


var query = @"SELECT AL.Title, AL.Price, AL.AlbumArtUrl, GE.Name, GE.[Description], AR.Name FROM Album AL INNER JOIN Genre GE ON AL.GenreId = GE.GenreId INNER JOIN Artist AR ON AL.ArtistId = AL.ArtistId";

var res = connection.Query<Album, Genre, Artist, Album>(query, (album, genre, artist) => { album.Genre = genre; album.Artist = artist; return album; }, commandType: CommandType.Text, splitOn: "ArtistId, GenreId");

I have checked for solution regarding this, non of it worked. Can anyone please let me know where I have gone wrong?

Thanks @Alex :) But I am still struck. This is what I have done:

CREATE TABLE Artist
(
ArtistId INT PRIMARY KEY IDENTITY(1,1)
,Name VARCHAR(50)
)

CREATE TABLE Genre
(
    GenreId INT PRIMARY KEY IDENTITY(1,1)
    ,Name VARCHAR(20)
    ,[Description] VARCHAR(1000)
)

CREATE TABLE Album
(
    AlbumId INT PRIMARY KEY IDENTITY(1,1)
    ,GenreId INT FOREIGN KEY REFERENCES Genre(GenreId)
    ,ArtistId INT FOREIGN KEY REFERENCES Artist(ArtistId)
    ,Title VARCHAR(100)
    ,Price FLOAT
    ,AlbumArtUrl VARCHAR(300) 
)

INSERT INTO Artist(Name) VALUES ('Jayant')
INSERT INTO Genre(Name,[Description]) VALUES ('Rock','Originally created during school days. The year was.....I guess 1998')
DECLARE @gen_id INT
        ,@art_id INT
SET @gen_id = (SELECT MAX(GenreId) FROM Genre)
SET @art_id = (SELECT MAX(ArtistId) FROM Artist)
INSERT INTO Album(GenreId,ArtistId,Title,Price,AlbumArtUrl) VALUES (@gen_id,@art_id,'I go mad for you',200,'http://asha4u.com/IGoMad')

As suggested by you I changed the query to:

var query = @"SELECT AL.AlbumId, AL.Title, AL.Price, AL.AlbumArtUrl, GE.GenreId, GE.Name, GE.Description, AR.ArtistId, AR.Name FROM Album AL INNER JOIN Artist AR ON AR.ArtistId = AL.ArtistId INNER JOIN Genre GE ON GE.GenreId = AL.GenreId";

var res = connection.Query<Album, Genre, Artist, Album>(query, (album, genre, artist) => { album.Genre = genre; album.Artist = artist; return album; }, commandType: CommandType.Text, splitOn: "GenreId, ArtistId");

Now I am using splitOn for GenreId and ArtistId. I still get the same error. Please help.

Popular Answer

You need to include the column you'd want to split on in your select query. Yours just selects all the other properties - so Dapper doesn't find a matching column to split the objects.

Your query should probably be something like that:

var query = @"SELECT AlbumId, Title, Price, AlbumArtUrl, GenreId, Name, Description , ArtistId, Name ......" etc

Sam wrote an excellent answer for multi mappings and the splitOn option: https://stackoverflow.com/a/7478958/1028323

Edit: If your query is as mentioned above, you'll have to split on GenreId and ArtistId.

 AlbumId, Title, Price, AlbumArtUrl | GenreId, Name, Description | ArtistId, Name

The pipes are for the start of a new POCO you're trying to map. So the SplitOn parameters would be GenreId and ArtistId.

Edit2: The problem is your POCO Album. You specify ArtistId and GenreId as properties but they basically belong to their respective POCO's.

public class Album
    {
        public virtual int AlbumId { get; set; }
        public virtual string Title { get; set; }
        public virtual decimal Price { get; set; }
        public virtual string AlbumArtUrl { get; set; }
        public virtual Genre Genre { get; set; }
        public virtual Artist Artist { get; set; }
    }

and

var sql = @"SELECT AL.AlbumId, AL.Title, AL.Price, AL.AlbumArtUrl, GE.GenreId, GE.Name, GE.Description, AR.ArtistId, AR.Name FROM Album AL INNER JOIN Artist AR ON AR.ArtistId = AL.ArtistId INNER JOIN Genre GE ON GE.GenreId = AL.GenreId";
            using (var conn = connFactory.OpenConnection())
            {
                var res = conn.Query<Album, Genre, Artist, Album>(sql, (album, genre, artist) =>
                {
                    album.Genre = genre;
                    album.Artist = artist;
                    return album;
                }, splitOn: "GenreId,ArtistId");
            }

should do the trick. You don't need GenreId and ArtistId anyway because you have a reference to those objects in Albums.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why