Dapper One-to-Many Relationship

In a relational database, 2 tables can be related with a Primary-Foreign key constraints. A very common relationships is One-to-Many where each record of one table are related to zero or more records of another table. In dapper we execute SQL JOIN Query with Dapper Query Method to retrieve records from the two table which are in One-to-Many relationship.

Learn Dapper from the complete series”

1. Dapper Execute Method – Execute SQL and Stored Procedure
2. Dapper Query Method – Execute SQL and Stored Procedure
3. Dapper One-to-One Relationship
4. Dapper One-to-Many Relationship
5. How to Seed Database with Dapper
6. How to perform CRUD Operations in Dapper

Let’s now understand records retrieval process in Dapper based on tables that are in One-to-Many relationship.

Dapper One-to-Many Relationship

We have 2 tables which are in One-to-Many relationship, these are:

1. Employee
2. Address

Each employee in the Employee table can have zero or many addresses in the Address table. Similarly, many Addresses can be of a particular Employee.

CREATE TABLE [dbo].[Employee] (
    [Id]     INT          IDENTITY (1, 1) NOT NULL,
    [Name]   VARCHAR (50) NOT NULL,
    [Email]  VARCHAR (50) NOT NULL,
    [Salary] MONEY        NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[Address] (
    [Id]         INT          IDENTITY (1, 1) NOT NULL,
    [EmployeeId] INT          NOT NULL,
    [HouseNo]    INT          NOT NULL,
    [Street]     VARCHAR (50) NOT NULL,
    [City]       VARCHAR (50) NOT NULL,
    CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Address_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [dbo].[Employee] ([Id])
);

The One-to-Many relationship between the 2 tables is shown below:

Dapper Query method for One-to-Many Relationship

Through Dapper Query method we can read records from One-to-Many relationship based tables. We simply apply Inner Join sql query and then map the result to strongly typed objects.

Create 2 classes Employee.cs and Address.cs. To these classes the result set of the Inner Join query will be mapped.

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public int Salary { get; set; }
    public List<Address> Address { get; set; }
}

public class Address
{
    public int Id { get; set; }
    public int EmployeeId { get; set; }
    public int HouseNo { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
}

Note that the Employee class has a list<Address> type property which will be filled with the multiple addresses an employee is having. Next, see the code of the Query method which executes an INNER JOIN query to fetch Employee and his/her related Address records.

//connection string
string myCS = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=DapperStart;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

string sql = "SELECT * FROM Employee AS A INNER JOIN Address AS B ON A.Id = B.EmployeeID;";

using (var connection = new SqlConnection(myCS))
{
    var dictionary = new Dictionary<int, Employee>();


    var list = connection.Query<Employee, Address, Employee>(
    sql,
    (employee, address) =>
    {
        Employee e;

        if (!dictionary.TryGetValue(employee.Id, out e))
        {
            e = employee;
            e.Address = new List<Address>();
            dictionary.Add(e.Id, e);
        }

        e.Address.Add(address);
        return e;
    },
    splitOn: "Id")
    .Distinct()
    .ToList();
}

Explanation: In this example, the input parameters of query method – Query() are Employee and Address. The return type is Employee. So Query method will take an Employee and a Address, process them, and return an Employee.

Next, see the Func delegate where the processing of the resultset is done:

(employee, address) =>
{
    Employee e;

    if (!dictionary.TryGetValue(employee.Id, out e))
    {
        e = employee;
        e.Address = new List<Address>();
        dictionary.Add(e.Id, e);
    }

    e.Address.Add(address);
    return e;
}

We used a dictionary for going through each record of the result set. Then we are adding the list of Address to the “Address” property of the Employee. In the end we are returning the Employee object.

The splitOn argument tells to split the record set on the Id column which is the first column of the Address table.

Let’s now check the working of this code. We place a breakpoint and checked the value of the list variable. The first Employee record’s Address Property is filled with it’s two related Address records. See the below image.

This means the code is working properly.

Download source codes:

Download