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

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.

Answer 1

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.

Answer 2

I have faced same problem. Here is trick & example.

public abstract class BaseEntity {     [Key]     public int Id { get; set; } }  public class Category : BaseEntity {     public string Name { get; set; } }   public class Status : BaseEntity {     public string Name { get; set; } }  public class User : BaseEntity {     public string Name { get; set; }     public string Surname { get; set; }     public bool Active { get; set; } }   public class TodoItem : BaseEntity {     public string Title { get; set; }     public string Message { get; set; }     public Status Status { get; set; }     public Category Category { get; set; }     public User User { get; set; }     public DateTime CreatedOn { get; set; } } 

Using

            string sql = @"select                            t.Id,                           t.Title,                           t.Message,                           t.CreatedOn,                           s.Id as Id,                           s.Name,                            c.Id as Id,                           c.Name,                            u.Id as Id,                           u.Name,                           u.Surname,                           u.Active                           from ToDoItem t                         inner join [Status] s on (t.StatusId = s.Id)                         inner join [Category] c on (t.CategoryId = c.Id)                         inner join [User] u on (t.AssignUserId = u.Id)";             var result = connection.Query<TodoItem, Status, Category, User, TodoItem>                 (sql, (todoItem, status, category, user) =>             {                 todoItem.Status = status;                 todoItem.Category = category;                 todoItem.User = user;                 return todoItem;             },splitOn: "Id,Id,Id,Id"); 

Here is trick splitOn: "Id,Id,Id,Id"