Multi‑table JOIN relational query

Dapper Query<T1,T2,TReturn>() implements one‑to‑one table join mapping

1. Syntax explanation: Query<PrimaryEntity,RelatedEntity,ReturnEntity>(sql, mapping delegate, parameters)
2. Mapping delegate: accepts the two entities fetched from the query, assigns related properties manually, then returns the primary entity
3. SQL guidelines: JOIN queries require explicit column listing. Use distinct table aliases to avoid conflicts for columns with identical names
4. Model design: add properties of child entities inside primary entities for object nesting (Book contains a Customer instance)

Example

1. Entity Class Definition

public class Customers
{
    public int CustomerID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
}

public class Book
{
    public int ID { get; set; }
    public string Title { get; set; }
    // Nested associated object: one book belongs to exactly one customer (one‑to‑one)
    public Customers Customer { get; set; }
}Code language: C# (cs)

2. Query Implementation

using System.Data;
using MySql.Data.MySqlClient;
using Dapper;
using System.Configuration;

class Program
{
    static void Main(string[] args)
    {
        var connStr = ConfigurationManager.ConnectionStrings["CustomerConnection"].ConnectionString;
        using (IDbConnection connection = new MySqlConnection(connStr))
        {
            var sql = @"select b.*,c.CustomerID,c.FirstName,c.LastName,c.Email
                        from Customers as c
                        join Book as b
                        on c.CustomerID = b.CID
                        where b.ID = @id;";

            var result = connection.Query<Book, Customers, Book>(
                sql,
                (book, custom) =>
                {
                    // Manually assign nested object
                    book.Customer = custom;
                    return book;
                },
                new { id = 1 }
            );
        }
        Console.ReadKey();
    }
}Code language: C# (cs)

Note: Generic signature Query<Book, Customers, Book>

• 1st argument: mapped type for first table, Book
• 2nd argument: mapped type for second table, Customers
• 3rd argument: final return data type, Book

Multi‑table JOIN relational query