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.

score 57 · Answer 1 · edited Dec 28 '18 at 14:39

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.

after making changes suggested by you, it still did not work. I changed the query considering only Artist and Album class. query = "SELECT AR.ArtistId, AR.Name, AL.AlbumId, AL.Price, AL.Title, AL.AlbumArtUrl FROM Artist AR INNER JOIN Album AL ON AR.ArtistId = AL.AlbumId"; var item = connection.Query(query, (album, artist) => { album.Artist = artist; return album; }, commandType: CommandType.Text, splitOn: "ArtistId"); It still did not work — user2497013, Jun 18 '13 at 18:18
@user2497013 See my edit. Easiest way is to form your SQL that it matches the POCO's you're trying to match in the right order. Currently, you're splitting on ArtistId - that tells Dapper to try to map all properties after ArtistId to one POCO which is not correct. — Alex, Jun 18 '13 at 20:01
I changed the query but still not working. 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(query, (album, genre, artist) => { album.Genre = genre; album.Artist = artist; return album; }, commandType: CommandType.Text, splitOn: "GenreId, ArtistId"); Please help. — user2497013, Jun 20 '13 at 04:30
The problem lies in your POCO. You need to remove GenreId and ArtistId from Album or Dapper ends up in undefined behavior :) — Alex, Jun 20 '13 at 06:12
Many Many thanks for your efforts.. Finally the code worked :) Removing ArtistId and GenreId from POCO Album did the trick. Cheers!!! — user2497013, Jun 20 '13 at 15:43

score 5 · Answer 2 · answered Oct 14 '19 at 12:31

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"

score 1 · Answer 3 · answered Sep 16 '21 at 18:08

1

I had this issue too, and found the problem was that I wasn't selecting the .Id column for each of my tables in the select

answered Sep 16 '21 at 18:08

David

11
1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-ask). – Community Sep 16 '21 at 22:33

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

3 Answers3

Linked