Dapper One-to-Many Relationship

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.

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 Address can be of a particular Employee.

The Employee table is:
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)
);
The Address table is:
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-One-to-Many Relationship

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.

Employee.cs and Address.cs

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

type property which will be filled with the multiple address an employee is having.

Next, see the code of the Query method which executes an INNER JOIN query to fetch Employee and it’s 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 a Employee.

Next, see the Func delegate where the processing of the result-set 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.

Dapper One-to-Many

This means the code is working properly.

Download source codes:

Download

SHARE THIS ARTICLE

  • linkedin
  • reddit
yogihosting

ABOUT THE AUTHOR

I hope you enjoyed reading this tutorial. If it helped you then consider buying a cup of coffee for me. This will help me in writing more such good tutorials for the readers. Thank you. Buy Me A Coffee donate

Leave a Reply

Your email address will not be published. Required fields are marked *