Dapper One-to-One Relationship

Dapper One-to-One Relationship

In a relational database, tables are linked to each other through primary and foreign key constraints. To fetch related records from these linked tables, we use the SQL JOIN command. In Dapper, the Dapper Query method lets us run a SQL query containing a JOIN and retrieve the related records.

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

Dapper One-to-One Relationship

In a one-to-one relationship, each record in the first table is linked to zero or one record in the second table. Here are two tables that share a one-to-one relationship:

  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.

The Invoice table is:
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)
);
The InvoiceDetail table is:
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])
);
Dapper Execute method is used for exceuting an SQL query or Stored Procedure. It returns number of rows affected in the execute operation. Get the complete knowledge from this tutorial.

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

One to One Relationship Dapper

To get the records from both tables, a SQL INNER JOIN query is executed with the Dapper Query method, and the result is mapped to two strongly typed classes, – Invoice.cs and InvoiceDetail.cs.

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 decimal 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” (3rd parameter) 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 populates the InvoiceDetail property of the Invoice.cs class with the related record returned by the INNER JOIN query, which comes from the InvoiceDetail table.

Next, notice SplitOn: “InvoiceId”. It tells Dapper where to split the result of the INNER JOIN query: the columns before “InvoiceId” map to the first type, “Invoice”, and the columns from “InvoiceId” onward map to the second type, “InvoiceDetail”.

We placed a breakpoint to test the code, and you can see that the “InvoiceDetail” property of “Invoice.cs” is filled with the related record. See below.

Dapper One-to-One Relationship

Download source codes:

Download

Conclusion

In this tutorial, we learned how to retrieve records from two tables that are in a One-to-One relationship using the Dapper Query method. We executed a SQL INNER JOIN query and mapped the result set to the Invoice and InvoiceDetail classes. The Func delegate assigned each related InvoiceDetail record to the InvoiceDetail property of its Invoice, and the splitOn argument told Dapper where the InvoiceDetail columns begin in each row.

Remember that an INNER JOIN returns only the invoices that have a detail record. To include invoices without one, use a LEFT JOIN and check for null inside the Func delegate.

Best Practices

Keep the following points in mind when working with One-to-One relationships in Dapper:

  • Make the foreign key unique. In the InvoiceDetail table, InvoiceId is both the primary key and the foreign key. This is what enforces the One-to-One relationship, because each invoice can have at most one detail record. If you use a separate Id column instead, add a UNIQUE constraint on the foreign key.
  • Choose the right join. An INNER JOIN returns only the invoices that have an InvoiceDetail record. Since the relationship allows zero or one detail record, use a LEFT JOIN if you also want invoices without details, and handle the null InvoiceDetail in your code.
  • Set splitOn correctly. The splitOn column must be the first column of the second table in the result set. Here it is InvoiceId. A wrong value causes incorrect mapping or empty properties.
  • Select columns explicitly. Avoid SELECT *. Listing the columns keeps their order predictable, which splitOn depends on, and it avoids fetching columns you do not need.
  • Skip Distinct(). In a One-to-One relationship, each row already returns a unique parent, so .Distinct() is not needed. Calling .ToList() is enough.
  • Match data types. Map SQL MONEY to decimal, SQL DATE to DateTime, and VARCHAR to string. A mismatch can cause conversion errors at runtime.
  • Use parameters, never string concatenation. When filtering, pass values through an anonymous object such as new { Id = 1 } to prevent SQL injection.
  • Dispose connections. Wrap the connection in a using block so it is closed and released properly.
  • Do not store the connection string in code. Keep it in appsettings.json or a secrets store, and read it through configuration.

Frequently Asked Questions

What is a One-to-One relationship?

It is a relationship in which one record of a table is linked to zero or one record of another table. For example, each invoice can have one invoice detail record. It is implemented by making the foreign key in the second table unique, or by using it as the primary key.

How does Dapper map a One-to-One relationship?

Dapper uses the Query method with a multi-mapping Func delegate. You run a JOIN query, and Dapper maps each row to two objects, Invoice and InvoiceDetail. In the delegate, you assign the InvoiceDetail object to the InvoiceDetail property of the Invoice object and return it.

What does the splitOn argument do?

It tells Dapper where the columns of one object end and the columns of the next object begin in each row. In this tutorial, splitOn: “InvoiceId” means the columns before InvoiceId are mapped to Invoice, and the columns from InvoiceId onward are mapped to InvoiceDetail. If you do not specify it, Dapper splits on a column named Id by default.

Why is the InvoiceDetail property null or empty?

The most common cause is a wrong splitOn value. Check that the column name matches exactly and that it is the first column of the second table in the result set. Another cause is a LEFT JOIN where the invoice has no detail record, in which case the property is null.

Why are some invoices missing from my results?

You are probably using an INNER JOIN, which returns only the invoices that have a matching InvoiceDetail record. Switch to a LEFT JOIN to include invoices without details.

Is Distinct() required in a One-to-One query?

No. Each invoice appears in only one row, so there are no duplicates to remove. .Distinct() is needed only in One-to-Many queries, where the parent repeats on every child row.

Can I map more than two tables with Dapper?

Yes. Dapper supports multi-mapping with up to seven types in a single Query call. Add each type as a generic argument and list the split columns in splitOn, separated by commas.

What is the difference between One-to-One and One-to-Many mapping in Dapper?

In One-to-One mapping, you simply assign the second object to a property of the first object. In One-to-Many mapping, you also need a dictionary to avoid duplicate parent objects and a list property to collect the child objects.

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 *