Strongly Typing Dapper Queries Using nameof

tldr;

Use nameof in order to strongly type your Dapper queries, so renaming a property on your C# doesn’t break your Dapper query or require you to remember to go fix them.

 

Before Stringly Typed (note lines 12-14):

 

After Strongly Typed (note lines 12-14):

 

What is Dapper?

Dapper is a lightweight micro-ORM, made by the Stack Exchange team, that transforms your SQL into strongly typed objects.  No longer do you have to deal with DataTables, DataSets, DataReaders, and the like.  You can just deal with your objects like a Customer object with FirstName, LastName, and ZipCode properties.

Dapper is a great alternative to a more full-featured ORM like Entity Framework.  Sometimes, Entity Framework is too much for a query (because the LINQ is hard/impossible to write) or sometimes it’s just overkill.

Dapper is implemented as a set of extension methods hanging off of DbConnection and a simple example is below:

Dapper will open and close the connection for you, parameterize your SQL query, and then it will hydrate the Customer object’s FirstNameLastName, and ZipCode properties, because they match the SQL column names.

But let’s say you have SQL column names that quite don’t match your C# properties like F_NameL_Name, and Zip_Code.  No big deal, all you do is alias the column name to match the C# property like below (lines 12-14):

As you can see, it’s pretty simple to use and results in super clean code.  Dapper is also much faster than Entity Framework, because it’s closer to the metal, but you also give up some features compared to something like Entity Framework such as LINQ queries, Change Tracking, Query Filters, Migrations, etc.

It’s really common for the projects I work on to use Dapper and EF together depending on what the best tool is for the job.

 

Downsides of Dapper

However, there are of course downsides to Dapper compared to Entity Framework.  With Entity Framework, you can be almost 100% guaranteed that your query won’t blow up when you go to run it (short of a schema problem, but that would be an issue in any data access library).

With Dapper, it’s up to you to write the correct SQL in a string.  If you misspell a column name, the C# compiler won’t yell at you, and you won’t find out until you go to run it.  Same goes for all the normal SQL goof ups like inserting too many or too few commas, forgetting a space, etc.

To  mitigate this, I like to write my queries in SQL Management Studio, with the help of RedGate’s SQL Prompt extension, and then copy and paste that query into my C# code to use with Dapper.

 

Problem When Renaming C# Properties

Unfortunately, using SQL Management Studio to build your queries doesn’t solve all the problems.  Let’s say I want to rename the ZipCode property to ZipCode9 to be more obvious that this is a 9 digit Zip Code and not a 5 or 4 digit one.  The problem is when I rename my property, if I don’t remember to go fix the Dapper query, then the ZipCode9 property will be null and everything will fail silently.  This is…. less than ideal.

 

Solution

To fix this, we can use the C# 6 feature nameof which lets you get the name of a variable, property, etc.  So if I change the query to look like below:

This will drop the Customer. at the beginning and just leave you with ZipCode9.  So it ends up being the exact same query that we had before, but now it’s strongly typed.

Now when you go to rename a property on your C# model, either your editor (like Visual Studio, Rider, etc.) will rename the property for you.  Or… if you forget to let your editor do the hard work for you, then your app won’t even compile and it’ll tell you this field is broken.  This effectively gives you strongly typed queries for your Dapper code and eliminates one of the headaches that most people have with Dapper.

Obviously, it comes at some cost of readability, but after using this approach for a few months, I can sift through the noise really quick and it’s saved me a handful of times.

 

Conclusion

A few months ago I tweeted this:

This was in reference to this nameof solution.  I fully recognize some of you will love this, some of you will hate this, and some of you will sit in the middle like I did originally.

That said, members of my team and I have started using this approach as our default and I’ve actually been fairly happy with it.  Like I said earlier, I can sift through the nameof noise quickly and the strongly typedness has already saved me a handful of times.

 

Hope this helps!

4 thoughts on “Strongly Typing Dapper Queries Using nameof

  1. I’ve used this “strategy” in the past and it works. You can also “refactor-proof” the table name by getting the value of the @Table attribute of the class but then you must use the @Table attribute on every entity class.

    However I believe the EF Core performance is on pair with Dapper so there isn’t really any reason to use Dapper over EF anymore, if you ask me 🙂

    Liked by 1 person

  2. Thanks for your comment, Snæbjørn. I agree – performance is less of an issue with EF Core.

    I do still reach for Dapper when I have a small app and I don’t want the boilerplate of the EF Configuration. Or if I’ve got some really complex query that I want to map to a DTO and not an Entity using the .FromSql(), and it’s easier to write it in SQL than LINQ. But like you, I definitely default to using EF and dropping to Dapper is more the exception.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s