If I using Dapper multi-mapping method, why do I need to specify the splitOn parameter if I set a key other than Id?

c# dapper

Question

I'm attempting to return a list of Albums along with any linked Artists and Genres using Dapper's Multi-mapping functionality.

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 looked for a solution, but none of them worked. Please let me know where I went wrong if you can.

Thank you, Alex:) But I remain astounded. What I did was as follows:

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

I amended the question as you recommended 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");

I now use splitOn for ArtistId and GenreId. The mistake remains the same. Thank you.

1
30
9/17/2019 7:12:19 AM

Popular Answer

The column you wish to divide on must be included in your select query. Yours only chooses all the other characteristics, thusDapper fails to locate a suitable column to divide the items into.

You should probably ask a question along these lines:

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

Sam came up with a fantastic response for multiple mappings and the splitOn option: https://stackoverflow.com/a/7478958/1028323

Edit: You must divide on ArtistId and GenreId if your query is as stated above.

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

The pipes are for the beginning of a brand-new POCO that you are attempting to map. So theSplitOn GenreId and ArtistId would be the parameters.

Edit2: Your POCO is the issue.Album . You defineArtistId and GenreId as attributes, but in reality, they are their respectivePOCO'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 work as intended. You don't needGenreId and ArtistId yet, considering that you have a reference to those items inAlbums .

44
12/28/2018 2:39:03 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow