Querying data from multiple tables using Dapper

Question

I have the database table GTL_TITLES which has two foreign keys, AuthorId and PublisherId. If I want to query a title from the database, I want to also get the information from the AUTHOR and PUBLISHER tables. For this purpose, I created a stored procedure that joins all three tables and selects the following columns:

My GtlTitle Model class looks like this:

public string ISBN { get; set; }
public string VolumeName { get; set; }
public string TitleDescription { get; set; }
public string PublisherName { get; set; }    
public DateTime PublicationDate { get; set; }
public Author TitleAuthor { get; set; }
public Publisher Publisher { get; }

As you could have guessed, class Author has two strings: FirstName and LastName and Publisher has PublisherName.

These being said, this is the method calling the database:

public GtlTitle GetTitle(string ISBN)
    {
        using (var connection = new SqlConnection(_connection))
        {
            connection.Open();
            return connection.QuerySingle<GtlTitle>("GetTitleByISBN", new { ISBN }, commandType: CommandType.StoredProcedure);
        }
    }

And returns the following: {"isbn":"978-0-10074-5","volumeName":"Volume Name - 97581","titleDescription":"Description - 97581","publisherName":"Publisher - 714","publicationDate":"2020-05-23T00:00:00","titleAuthor":null,"publisher":null}

As you can see, titleAuthor and publisher are null. How can I fix this? Will I need to write fields like public string FirstName in the GtlTitle model class instead or is there any way of populating the Author and Publisher as well?

Does this answer your question? [How do I map lists of nested objects with Dapper](https://stackoverflow.com/questions/7508322/how-do-i-map-lists-of-nested-objects-with-dapper) — Crowcoder, May 25 '20 at 11:07
@Crowcoder This is not a case for nesting, more like a one to one mapping. — Alex, May 25 '20 at 11:09
Looks like a complex object query. You just need to tell Dapper where each object is. See this link: https://dapper-tutorial.net/knowledge-base/44980945/querying-into-a-complex-object-with-dapper — mb14, May 25 '20 at 11:12
@Alex `splitOn` is mentioned a couple of times in the link I posted. — Crowcoder, May 25 '20 at 11:18
@Crowcoder Yes it is mentioned but the author of the current question doesn't have a nested relationship, just a simple one-to-one map. — Alex, May 25 '20 at 12:38

score 2 · Accepted Answer · answered May 25 '20 at 11:14

Dapper supports multimapping with the splitOn parameter where you can split a row into mulitple objects by providing the column names where a new object begins.

return connection.Query<GtlTitle, Author, Publisher, GtlTitle>(sql,
    (g,a,p) => { 
                 g.TitleAuthor = a; 
                 g.Publisher = p; 
                 return g; }, 
    splitOn: "FirstName,PublisherName").First();

Querying data from multiple tables using Dapper

1 Answers1