C# Blog

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.

Entities:

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>(
            query,
            (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
        .ToList();

    // 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;
        })
        .ToList();

    return (books, authors);
}

• Dapper
• 30-04-2022