Dapper In .NET Core – Part 4 – Dapper Contrib

This article is a series on using Dapper in .NET Core. Feel free to jump around, but we highly recommend you start in Part 1 and work your way through!

Part 1 – The What/Why/Who
Part 2 – Dapper Query Basics
Part 3 – Updates/Inserts/Deletes
Part 4 – Dapper Contrib

What Is Dapper Contrib?

Dapper.Contrib is a “helper” library built on top of Dapper, that adds a couple of “quality of life” enhancements to otherwise annoying Dapper hurdles. Most notably, updates and inserts become much simpler with Dapper.Contrib. So let’s jump right in!

Installing Dapper.Contrib

From your Package Manager console window you can install the Dapper.Contrib package.

Install-Package Dapper.Contrib

If you haven’t already installed Dapper, this will drag Dapper in as a dependency too! Note that at times you do have to do a juggle with versions of Dapper if you install it first, then Contrib second. The easiest way if you really want to use Contrib is remove the direct reference to the Dapper nuget, and simply install Contrib and let it drag in the version it needs.

Model Our C# Class For Writes In Dapper.Contrib

The important thing when using Dapper.Contrib is that you need a C# model to match that of your database model. Again I can’t stress enough that this should really only be used in the case of writes. When reading data, the entire point of using Dapper is that you don’t over-select and so you should attempt to use applicable DTO’s whenever possible.

From earlier, we have our C# model ready to go :

class Event
{
    public int Id { get; set; }
    public int EventLocationId { get; set; }
    public string EventName { get; set; }
    public DateTime EventDate { get; set; }
    public DateTime DateCreated { get; set; }
}

This works as is, but there are a couple of things to note.

Firstly, Dapper.Contrib requires an “Id” to be able to update a record. In our case, we have a property called “Id” and Dapper.Contrib uses this convention and knows it’s our primary key on our table. But what if we use something like “EventId” as our primary key? That’s easy too.

class Event
{
    [Key]
    public int Id { get; set; }
    public int EventLocationId { get; set; }
    public string EventName { get; set; }
    public DateTime EventDate { get; set; }
    public DateTime DateCreated { get; set; }
}

We just add the “Key” attribute from our Dapper.Contrib library to tell it that’s our primary key.

Next, we are also required that the class name be the same as the SQL Table name, but as a plural. So in our case, our class name is Event and it expects that our database table be called “Events”.  So how can we say nope, we want to stick with the singular?

[Table ("Event")]
class Event
{
    public int Id { get; set; }
    public int EventLocationId { get; set; }
    public string EventName { get; set; }
    public DateTime EventDate { get; set; }
    public DateTime DateCreated { get; set; }
}

Again, another attribute. There is actually a couple more attributes that do come in handy, but these are the main ones you should know right off the bat.

Inserting Records Using Dapper.Contrib

This should hopefully make things easier.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    var newEvent = new Event
    {
        EventLocationId = 1,
        EventName = "Contrib Inserted Event",
        EventDate = DateTime.Now.AddDays(1),
        DateCreated = DateTime.UtcNow
    };
    connection.Insert(newEvent);
}

… Pretty awesome right! So we are back to having this nice “Insert” method that takes our object and creates an insert statement. It takes some of that perfect control away from us, but when it comes to inserts there isn’t much that we would want to be careful of when it comes to generating the insert statement so I’m happy with it.

Get A Record By Id Using Dapper.Contrib

Contrib also has this nifty feature where it can get a full record by Id. I feel like this sort of strays a little from the intention of Dapper because you should be trying to avoid doing a “SELECT *” and instead only get what you need. But we’ll use it later so I wanted to show it off.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    var eventId = 1;
    var myEvent = connection.Get<Event>(eventId);
}

Because it knows we are requesting a type of “Event”, it knows what table that’s from and grabs us the entire object based on it’s primary key Id.

Updating Records Using Dapper.Contrib

Contrib can also do updates. Although they are a little less slick.

First off, we can obviously get our object by ID, update it, then write it like so :

using (SqlConnection connection = new SqlConnection(connectionString))
{
    var eventId = 1;
    var myEvent = connection.Get<Event>(eventId);
    myEvent.EventName = "New Name";
    connection.Update(myEvent);
}

So Contrib has given us that handy “Update” method. But the problem is when we check the actual SQL that got run :

exec sp_executesql N'update Event set [EventLocationId] = @EventLocationId, [EventName] = @EventName, [EventDate] = @EventDate, [DateCreated] = @DateCreated where [Id] = @Id',
N'@DateCreated datetime,@EventDate datetime,@EventLocationId int,@EventName nvarchar(4000),@Id int',@DateCreated='2019-01-20 02:11:46.453',@EventDate='2019-01-11 00:00:00',@EventLocationId=1,@EventName=N'New Name',@Id=1

So it basically updated all fields even if they weren’t touched. Because of this we have to get the record by Id before updating because we need to make sure all other fields (Not just the one we want to update) are set correctly.

Now Dapper.Contrib does have dirty tracking, but IMO that’s just as pointless as you still need to get the full object by ID in the first place to do entity tracking, and the entire point is that we aren’t overselecting.

In my opinion, Updates are still a good candidate for doing custom SQL instead of using Contrib, but that’s just my personal feeling!

Delete Records Using Dapper.Contrib

Deleting is actually fairly simple. As long as Dapper.Contrib knows what your primary key is, it can delete records based on that.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Delete(new Event { Id = 5 });
}

To be honest, this syntax annoys me somewhat as we are creating an object for no reason! Wouldn’t it just be easier to pass in a simple Int parameter?

What’s Next?

That’s actually it! We could get into the nitty gritty of Dapper, or even explore some of the other Dapper extension libraries such as Dapper.Rainbow or DapperExtensions, but they are largerly the same as Dapper.Contrib with minor differences. They are definitely worth investigating however!

Feel free to drop a comment below on your thoughts on Dapper and what project’s you’ve used it on in the past, I’d love to hear them!