Entity Framework Core has been a the heart of a lot of the post I have done recently. I thought it would be a good change of pace to try out one of the alternatives to Entity Framework Core and give Dapper a try.
I tried to find a good example of the difference between micro ORMs like Dapper and full ORMs like Entity Framework Core, but all the ones I found ended up pushing one option over the other. I’m not looking to say one is better than the other, but to add a new tool to my toolbelt.
Basically, the differences seem to come down to micro ORMs provide significantly fewer features than a full ORM but come with a higher level of performance. Which is the right choice is going to vary by project.
Sample Project
We will be using a new Razor Pages application as the starting point for the sample application. It currently doesn’t have any database access setup. The code for the starting point of the sample can be found here.
I’m going to be using an existing database created for the SQL Server example in my Entity Framework sample project. Below is a Contacts table generated from that sample project in case you want to manually generate the table.
CREATE TABLE [dbo].[Contacts] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (MAX) NULL,
[Address] NVARCHAR (MAX) NULL,
[City] NVARCHAR (MAX) NULL,
[Subregion] NVARCHAR (MAX) NULL,
[PostalCode] NVARCHAR (MAX) NULL,
[Phone] NVARCHAR (MAX) NULL,
[Email] NVARCHAR (MAX) NULL,
[Timestamp] ROWVERSION NULL
);
Add Dapper Nuget Package
Using your favorite way to add a NuGet package add a reference to the Dapper package. I’m going to do it using Visual Studio by right-clicking on the project and selecting Manage NuGet Packages.
On the next screen, from the Browse tab use the Search Box to search for Dapper. Select the Dapper package and click Install.
Add Connection String Configuration
We are going to store the database connection string in the appsettings.json file. Open the file and add a ConnectionString section with a DefaultConnection that contains the database connection string. This exact setup isn’t required I’m just following the way configuration is set up for Entity Framework base projects. The following is my full configuration file with the new section at the top.
{
"ConnectionStrings": {
"DefaultConnection": "Your connection string"
},
"Logging": {
"LogLevel": {
"Default": "Warning"
}
},
"AllowedHosts": "*"
}
Contact Model
Add a Contact class in a Models folder which will be used to represent our data. The following is the full model class I will be using in this example.
public class Contact
{
public int Id { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string Subregion { get; set; }
public string PostalCode { get; set; }
public string Phone { get; set; }
public string Email { get; set; }
}
Test Setup
To keep things simple I will not be dealing with any UI other than as a way to run the Dapper related code. As such we will be using the Page Modle of the Index page to run the sample code. This is in no way an example of the best practice setup and is only done to demo Dapper in the simplest setup possible.
The following is the full page model with the configuration for the application being injected via the constructor. All the changes passed this point will be in the OnGet function.
public class IndexModel : PageModel
{
private readonly IConfiguration _configuration;
public IndexModel(IConfiguration configuration)
{
_configuration = configuration;
}
public void OnGet()
{
}
}
Using Dapper
Dapper provides a number of extension methods off of the IDbConnection interface. For this example, we will use these extensions to check to see if a specific contact exists, if it doesn’t then insert it, and finally select the contact back out of the database.
The first step is to open a connection. In this example, we are using SQL Server, but Dapper doesn’t really care and will work with any ADO.NET provider. The following code opens a SQL connection using the connection string from appsettings.json.
using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
{
connection.Open();
}
Next, we are going to query the Contacts table for the ID of a specific contact.
if (connection.QueryFirstOrDefault<int?>(@"SELECT Id
FROM Contacts
WHERE Name = @Name",
new {Name = "Charlie Plumber"}) == null)
{
}
As you can see from the above you there is nothing special about the SQL being written. For this query, we are using an anonymous type to pass the query a name filter which will be translated to a SqlParameter which ensures the query won’t all things like SQL injection. Note that the parameter name in the SQL string must match the name of the corresponding property on the object being passed to the query.
This next example is the contact insert if the contact wasn’t found in the previous query.
connection.Execute(@"INSERT INTO Contacts (Name, Address, City, Subregion, Email)
VALUES (@Name, @Address, @City, @Subregion, @Email)",
new Contact
{
Name = "Charlie Plumber",
Address = "123 Main St",
City = "Nashville",
Subregion = "TN",
Email = "[email protected]"
});
Again the SQL is exactly what you would expect, which is one of the great parts about Dapper if you are familiar with SQL.
This last example is a combination of all the previous examples with a select to get out the contact details of the contact in question.
using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
{
connection.Open();
if (connection
.QueryFirstOrDefault<int?>(@"SELECT Id
FROM Contacts
WHERE Name = @Name",
new {Name = "Charlie Plumber"}) == null)
{
connection.Execute(@"INSERT INTO Contacts (Name, Address, City,
Subregion, Email)
VALUES (@Name, @Address, @City,
@Subregion, @Email)",
new Contact
{
Name = "Charlie Plumber",
Address = "123 Main St",
City = "Nashville",
Subregion = "TN",
Email = "[email protected]"
});
}
var charile =
connection.QueryFirstOrDefault<Contact>(@"SELECT Id, Name, Address,
City, Subregion, Email
FROM Contacts
WHERE Name = @Name",
new {Name = "Charlie Plumber"});
}
Wrapping Up
This first round of playing with Dapper was fun. I write a lot of SQL in my day job so it was kind of nice seeing the explicit SQL vs the magic of a full ORM. I can tell you from doing this sample I would miss Entity Framework’s database creation and migration capabilities.
The code in its final state can be found here.
Obviously, this example is really ugly and isn’t something you would do to your users, but it gives you the basic idea of how to use a custom page.
