Dapper is a simple, light-weight, and truly popular ORM (item relationship mapper) for.NET. We found out the fundamentals of dealing with Dapper in a previous post. And we talked about working with the Dapper Extensions library in an earlier article. More recently, we explored a few sophisticated functions of Dapper.
In this article, we’ll discover how to work with relationship mappings in Dapper. Particularly, we’ll analyze how to map one-to-one, one-to-many, and many-to-many relationships using Dapper.To utilize the code examples offered in this short article, you ought to have Visual Studio 2022 set up in your system. If you do not currently have a copy, you can download Visual Studio 2022 here. Produce an ASP.NET Core Web API job in Visual Studio To begin with, let’s develop an ASP.NET Core 7 project in Visual Studio 2022.
Follow these steps: Introduce the Visual Studio
2022 IDE. Click” Develop brand-new project.” In the” Create new job”window, select
- “ASP.NET Core Web API “from the list of design templates showed. Click Next. In the “Configure your brand-new job “window, define the name and place for the new task.
- Optionally check the “Location option and job in the same directory site”check box, depending upon your preferences. Click Next. In the “Extra Information”window revealed next, leave the” Use controllers(uncheck to utilize very little APIs)”box checked.
- We will not be utilizing very little APIs in this job. Leave the “Authentication Type”set to “None” (the default). Guarantee that the check boxes “Enable Open API Support, “”Set up for HTTPS,” and “Enable Docker”stay untreated. We will not be utilizing those features here. Click Create. We’ll use this ASP.NET Core 7 Web API task to work with sophisticated functions of Dapper in the areas below.What is Dapper? Why use it?Object-
- relational mappers are used
to deal with the” impedance inequality” between object designs in programs languages and information designs in relational databases.
Dapper is a lightweight, high performance, micro-ORM structure for.NET and.NET Core. It supports a vast array of databases such as Oracle, SQL Server, Oracle, MySQL, PostgreSQL, SQLite, and SQL CE. The Stack Overflow group created Dapper to be an easy ORM for.NET. Dapper is an open-source task offered on GitHub. Multi-mapping in Dapper offers assistance for multi-mapping, which allows you to map a single record from one database table to numerous records from another table. With multi-mapping, you can retrieve information from several database tables in one query.To achieve this, you utilize the SplitOn specification when you call Dapper’s Inquiry
approach. The SplitOn method
specifies which columns to use to divide information into numerous objects. Think about the following 2 classes. public class Author p>
public string LastName get; set; public class Book The following code snippet illustrates how you can execute multi-mapping utilizing Dapper.using(var connection= brand-new SqlConnection(connectionString) )string question= “SELECT * from Authors A Inner Join Books B ON A.Id =B.AuthorId”; var authors=connection.Query(inquiry,( author, book )=> , splitOn:”Id” ). Distinct( ). ToList (); Easy mapping in Dapper Consider a database table called Shop with the fields listed in the table listed below. Table Call: Store Field Call Field Type Store_Id( Primary_Key) Integer (Auto-generated )Store_Name Varchar Area Varchar– The following parameterized SQL query would return a record from the Store database table that has a matching Store_Id: Select Store_Id, Store_Name, Location From Shop Where Store_Id =@StoreId To
obtain a record from the Shop database table utilizing Dapper, utilize the following code snippet. utilizing var connection =new SqlConnection(
); var stores =await connection.QueryAsync
(sql, brand-new Store_Id=storeId
.); One-to-one relationship multi-mapping
in Dapper Think about two database tables called Order and
Client with the
fields listed
in the tables listed below. Table Name: Order Field Name Field Type
Order_Id(Primary_Key)Integer(Auto-generated)Customer_Id(Foreign_Key)Integer Order_Date DateTime Order_Quantity Integer Order_Price Double– Table Name: Customer Field Call Field Type Customer_Id( Primary_Key)Integer(Auto-generated)First_Name Varchar Last_Name Varchar Address Varchar Phone Varchar Email Varchar– You can use the following
SQL inquiry to choose an Order record and its associated Client details.Select o.Order _ Id, o.Order _ Date, c.Customer _ Id, c.First _ Name, c.Last _ Name From Order o Inner Join Consumer c On o.Customer _ Id =c.Customer
_ Id The following code demonstrates how you can establish a one-to-one mapping between the Order and Consumer database
tables and obtain
Order records using Dapper.string query=”
Select * From Order o Inner Join
Customer c On o.Customer
_ Id=c.Customer _ Id”; var orders
=connection.Query(query, map:(
order, consumer)=>
p> <, splitOn
:”Customer_Id”). FirstOrDefault();
One-to-many
relationship multi-mapping in Dapper We’ll now customize the
Order table to integrate an
extra
field. The
following database tables show a one-to-many relationship between two entities, Order and
Item, implying
you can have an
order that
is associated
with several
products. Table Call
: Order Field Name
Field Type Order_Id
(Primary_Key)Integer
(Auto-generated)Product_Id(Foreign_Key)Integer Customer_Id( Foreign_Key )Integer Order_Date DateTime Order_Quantity Integer Order_Price Double– Table Name: Product
Field Name Field Type Product_Id(Primary_Key)Integer(Auto-generated)Product_Name Varchar Product_Quantity Integer Unit_Price Double– You can now use the
following SQL question to retrieve all orders.Select Order_Id, Order_Quantity, Order_Date, p.Product _ Name From Order o Inner Join Product p On o.Product _ Id=p.Product _ Id The following code snippet shows how to establish a one-to-many mapping in between Order and Item tables and recover Order records utilizing Dapper.string inquiry=”Select * From
mapped to multiple students. In
other words, many-to-many relationships enable multiple
records in one table to be related to multiple
records in another table.Such relationships are normally executed using a 3rd table, which is
often called a”
junction table”or”bridge table.
“To map a
many-to-many relationship utilizing Dapper, you must deal with the join table and
make numerous SQL
queries
or one well-constructed
inquiry to fetch the associated
data.Consider the
three database tables named Student, Course,
and StudentCourse and their particular fields as
displayed in the tables listed below. Table
Call: Trainee Field Call Field Type Student_Id
(Primary_Key
)Integer(Auto-generated
)First_Name Varchar Last_Name
Varchar
Address Varchar
— Table Call:
Course Field Call Field Type Course_Id(Primary_Key)Integer (Auto-generated)Course_Title Varchar Course_Duration Integer Start_Date DateTime– Table Call: Student_Course Field Call Field Type Student_Course_Id (Primary_Key
)Integer(Auto-generated)Student_Id(Foreign_Key)Integer Course_Id (Foreign_Key)Integer– We can represent the relationship between trainees and courses using the following classes.public class Trainee public int Student_Id get; private set; public string FirstName get; set; =string.Empty; public string LastName public string Address =string.Empty; public List Courses get; set; public class Course public int Course_Id public string Course_Title public int Course_Duration get; set; public DateTime Start_Date public List Courses get; set; Keep in mind the lists in the Student and Course classes. While the Student class contains a list obviously, the Course class consists of a list of Students. You can use the following SQL statement to retrieve a trainee and its associated data.Select s.Student _ Id, s.First _ Name, s.Last _ Name, c.Course _ Title From Trainee s Inner Join Student_Course sc ON sc.Student _ Id
=s.Student _ Id Inner Join Course c ON c.Course _ Id=sc.Course _ Id You can establish a many-to-many mapping between the Student and Course entities
in Dapper that permits you to
retrieve student
records together
with the courses they have signed up for
. Here is the code: using(var connection
=new SqlConnection(connectionString))
table>
, course
)=> , splitOn
:”Course_Id”); < Dapper is a light-weight
, performant, and personalized ORM with
great functions that can considerably enhance
information access capabilities in
your.NET Core applications. As
we have actually seen, Dapper provides
excellent support for mapping relationships. I’ll have more to state about using Dapper in a future short article here. Copyright © 2023 IDG Communications, Inc. Source
