Dapper joins in Entity Framework style objects

Dapper and joins

Dapper supports joins but not in the way you are used with Entity Framework. With little extra code, the entity looks very much like Entity Framework.

I could not found simple example that have list of tuples where each tuple has 1 author and a list of books. I found an example ath the Dapper site Dapper multi mapping, but that a book that only has a reference to an author. Just like in the database, but I want it object oriented: the author also has a list of books. That is why I put an simple example of an object oriented style in this blog post.

The database tables

Author table.

Book table.


public record Author
    public int AuthorId { get; init; }
    public string FirstName { get; init; }
    public string FamilyName { get; init; }

    public IReadOnlyList<Book> Books { get; init; }

public record Book
    public int BookId { get; init; }
    public string Title { get; init; }
    public string Color { get; init; }

    public Author Author { get; init; }

The code for the join:

public (IReadOnlyList<Book> books, IReadOnlyList<Author> authors) GetAuthorsWithBooks(SqlConnection connection, object? parameters = null
    // Note parameters are not used here, but is might be needed in your example
    var query = @"SELECT a.id as AuthorId, a.FirstName, a.FamilyName, b.id as bookId, b.Title, b.Color
                  FROM Author a
                  INNER JOIN  Book b ON a.Id = B.AuthorId";

    // First create books from the query results
    var books = connection
        .Query<Author, Book, Book>(
            (author, book) =>
                var bookWithAuthor = book with { Author = author }; // Set the author of the books
                return bookWithAuthor;
            param: parameters,
            splitOn: "bookId") // The first column that is part of the second table

    // Map books to an Author from a list of books
    var authors = books.GroupBy(t => t.Author.AuthorId)
        .Select(g =>
            var authorWithBooks = g.First().Author with   // All books had same author, simply take the first
                Books = g.Select(book => book).ToList() // Get a list of all books of that author

            return authorWithBooks;

    return (books, authors);

Leave a Comment



C# CSharp Blog Comment

qr code donatie 06-12-2023 / Reply

"Fabulous drive! The QR code gift connect on this blog makes adding to the reason consistent. It's cheering to see innovation being utilized for social great. Praise to the group for driving positive change through the force of gifts! "