Dapper One-to-One Relationship

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 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.

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.

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])
);

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

One to One Relationship Dapper

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 list – 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 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 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.

Dapper One-to-One Relationship

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 *