11. Dapper Relationships

Dapper Relationships: One-to-One, One-to-Many, and Many-to-Many

Introduction

In this note, we will explore how to handle different types of relationships between tables in a relational database using Dapper, a micro ORM (Object-Relational Mapper) for .NET.

One-to-One Relationship in Dapper

A one-to-one relationship occurs between two entities when an instance of an entity is associated with only one instance of another entity. In Dapper, you can fetch related data using an INNER JOIN query.

Example: Fetching Data with INNER JOIN

Here's an example of fetching data from Capitals and Countries tables that have a one-to-one relationship:

var sql = @"SELECT Capitals.CapitalId,Capitals.Name,Capitals.CountryId,
            Countries.CountryId, Countries.Name
            FROM Capitals
            INNER JOIN Countries
            ON Capitals.CountryId = Countries.CountryId";

var capitals = connection.Query<Capital, Country, Capital>(sql,
                (capital, country) =>
                {
                    capital.Country = country;
                    return capital;
                }, splitOn: "CountryId").ToList();

In this example, the Query method is used to execute the SQL query. The splitOn parameter indicates the column at which Dapper should split the returned row into objects.

One-to-Many Relationship in Dapper

In a one-to-many relationship, an instance of an entity can be associated with multiple instances of another entity. You can use an inner join query to get data in such a relationship.

Example: Fetching Data with INNER JOIN

Here are two examples of fetching data from Students and Groups tables that have a one-to-many relationship:

// Example 1
var sql = @"SELECT S.StudentId, S.Firstname, S.Age,
            G.GroupId,G.Title
            FROM Students AS S
            INNER JOIN Groups AS G
            ON S.GroupId=G.GroupId";

var students = connection.Query<Student, Group, Student>(sql,
    (student, group) =>
    {
        student.Group = group;
        student.GroupId = group.GroupId;
        return student;
    }, splitOn: "GroupId").ToList();

// Example 2
var sql = @"SELECT  G.GroupId,G.Title,
            S.StudentId, S.Firstname, S.Age
            FROM Groups AS G
            INNER JOIN Students AS S
            ON S.GroupId=G.GroupId";

var groups = connection.Query<Group, Student, Group>(sql,
    (group, student) =>
    {
        group.Students.Add(student);
        student.GroupId = group.GroupId;
        student.Group = group;
        return group;
    }, splitOn: "StudentId").ToList();

In the first example, we fetch a list of students along with their group information. In the second example, we fetch a list of groups, each with a list of students belonging to that group.

Many-to-Many Relationship in Dapper

A many-to-many relationship occurs between entities when multiple instances of an entity can be associated with multiple instances of another entity.

Example: Fetching Data with INNER JOIN

Here's an example of fetching data from A and B tables that have a many-to-many relationship:

var As = connection.Query<A, B, A>(sql,
    (A, B) =>
    {
        A.Collection.Add(B);
        B.Collection.Add(A);
    }, splitOn: "ForeignKeyId").ToList();

In this example, each instance of A is associated with multiple instances of B, and vice versa.

Conclusion

Dapper provides flexible and efficient ways to handle different types of relationships between tables in a relational database. The Query method and INNER JOIN queries are key tools for fetching related data.

Additional Resources:

• Dapper One-to-One Relations

• Dapper One-to-Many Relations

Reference

The content in this document is based on the original notes provided in Azerbaijani. For further details, you can refer to the original document using the following link:

Original Note - Azerbaijani Version