Dapper One-to-One Relationship

In relational database, a table can be connected to another table using Primary-Foreign key constraints. In order to retrive connected records we apply SQL JOIN command. In Dapper we can apply the Dapper Query method to execute SQL query having JOIN command in order to get related records.

Let’s now understand in details how to work with One-to-One relationship in Dapper.

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

Dapper One-to-One Relationship

In One-to-One realationship one record of the first table will be linked to zero or one record of second table. We have 2 tables which have one-to-one relationship, these are:

1. Invoice
2. InvoiceDetail

Each Invoice record can have zero or one related InvoiceDetail record. Similarly, each InvoiceDetail record can have exactly one Invoice record.

CREATE TABLE [dbo].[Invoice] (
    [Id]           INT          IDENTITY (1, 1) NOT NULL,
    [CustomerName] VARCHAR (50) NOT NULL,
    [ItemName]     VARCHAR (50) NOT NULL,
    [Price]        MONEY        NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[InvoiceDetail] (
    [InvoiceId]           INT           NOT NULL,
    [BillingAddress]      VARCHAR (250) NOT NULL,
    [PaymentMethod]       VARCHAR (50)  NOT NULL,
    [SalesRepresentative] VARCHAR (250) NOT NULL,
    [AddedOn]             DATE          DEFAULT (getdate()) NOT NULL,
    PRIMARY KEY CLUSTERED ([InvoiceId] ASC),
    CONSTRAINT [FK_InvoiceDetail_Invoice] FOREIGN KEY ([InvoiceId]) REFERENCES [dbo].[Invoice] ([Id])
);

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

To get the records from both these tables, a SQL Inner JOIN query can be executed with Dapper Query method and then the result is mapped to 2 strongly typed lists – Invoice.cs and InvoiceDetail.cs.

First define the 2 classes – Invoice.cs and InvoiceDetail.cs.

public class Invoice
{
    public int Id { get; set; }
    public string CustomerName { get; set; }
    public string ItemName { get; set; }
    public int Price { get; set; }
    public InvoiceDetail InvoiceDetail { get; set; }
}

public class InvoiceDetail
{
    public int InvoiceId { get; set; }
    public string BillingAddress { get; set; }
    public string PaymentMethod { get; set; }
    public string SalesRepresentative { get; set; }
    public DateTime AddedOn { get; set; }
}

Notice the Invoice.cs has a property of InvoiceDetail type. We will later see how to fill this property with a value from the InvoiceDetail table.

Dapper Query method for One-to-One Relationship

Next, use the Query method to fetch the records from both the tables. The full code is given below:

//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 Invoice AS A INNER JOIN InvoiceDetail AS B ON A.Id = B.InvoiceId;";

using (var connection = new SqlConnection(myCS))
{

    var invoices = connection.Query<Invoice, InvoiceDetail, Invoice>(
            sql,
            (invoice, invoiceDetail) =>
            {
                invoice.InvoiceDetail = invoiceDetail;
                return invoice;
            },
            splitOn: "InvoiceId")
        .Distinct()
        .ToList();
}

Explanation: We used an inner join sql query which will be executed by the Query method. The query method has been defined as Query<Invoice, InvoiceDetail, Invoice> which specifies that it will take “Invoice and InvoiceDetail” parameters and then return “Invoice” type result.

See the Func delegate code which process the result of the Inner Join query.

(invoice, invoiceDetail) =>
{
    invoice.InvoiceDetail = invoiceDetail;
    return invoice;
}

The Func delegate code fills the value of InvoiceDetail property of the Invoice.cs class which the Inner Join query returned. That is, it fills the related record from the InvoiceDetail table.

Next, see the SplitOn: “InvoiceId” which tells Dapper to split the inner join query result on the “InvoiceId” column. Anything up to that column maps to the first “Invoice” type, and anything else from that column onward should be mapped to the second “InvoiceDetail” type.

We applied breakpoint to test the code. You can see it fills the “InvoiceDetail” property of “Invoice.cs” with the related records. See below.

Download source codes:

Download