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 — Crowcoder
– Crowcoder, Commented May 25, 2020 at 11:07
@Crowcoder This is not a case for nesting, more like a one to one mapping. — Alex
– Alex, Commented May 25, 2020 at 11:09
Looks like a complex object query. You just need to tell Dapper where each object is. See this link: dapper-tutorial.net/knowledge-base/44980945/… — mb14
– mb14, Commented May 25, 2020 at 11:12
@Alex splitOn is mentioned a couple of times in the link I posted. — Crowcoder
– Crowcoder, Commented May 25, 2020 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
– Alex, Commented May 25, 2020 at 12:38

Alex · Accepted Answer · 2020-05-25 11:14:58Z

2

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();

answered May 25, 2020 at 11:14

Alex

8,1561 gold badge45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying data from multiple tables using Dapper

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related