CODEWITHSUNDEEP
c#sql-serverapi.net-coredapper
Questieme
Questieme

Reputation: 993

Querying data from multiple tables using Dapper

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:

enter image description here

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?

Upvotes: 0

Views: 3557

Answers (1)

Alex
Alex

Reputation: 8116

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

Upvotes: 2

Related Questions