Strongly Typing Dapper Queries Using nameof

Scott Sauber4 Comments

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):

 

 

 


public class Customer
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string ZipCode { get; set; }
}
public List<Customer> GetCustomersByState(string state)
{
var dbConnection = new SqlConnection("SomeConnectionString");
string sql = @"SELECT F_Name AS FirstName,
L_Name AS LastName,
Zip_Code AS ZipCode
FROM Customers
WHERE State = @State";
var parameters = new DynamicParameters();
parameters.Add("State", state);
return dbConnection.Query<Customer>(sql, parameters).ToList();
}

view raw

CustomerQueriesAliases.cs

hosted with ❤ by GitHub

 

After Strongly Typed (note lines 12-14):

 

 


public class Customer
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string ZipCode { get; set; }
}
public List<Customer> GetCustomersByState(string state)
{
var dbConnection = new SqlConnection("SomeConnectionString");
string sql = $@"SELECT F_Name AS {nameof(Customer.FirstName)},
L_Name AS {nameof(Customer.LastName)},
Zip_Code AS {nameof(Customer.ZipCode)}
FROM Customers
WHERE State = @State";
var parameters = new DynamicParameters();
parameters.Add("State", state);
return dbConnection.Query<Customer>(sql, parameters).ToList();
}

view raw

CustomerQueriesWithNameOf.cs

hosted with ❤ by GitHub

 

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:

 

 

 

 

 


public class Customer
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string ZipCode { get; set; }
}
public List<Customer> GetCustomersByState(string state)
{
var dbConnection = new SqlConnection("SomeConnectionString");
string sql = @"SELECT FirstName,
LastName,
ZipCode
FROM Customers
WHERE State = @State";
var parameters = new DynamicParameters();
parameters.Add("State", state);
return dbConnection.Query<Customer>(sql, parameters).ToList();
}

view raw

CustomerQueries.cs

hosted with ❤ by GitHub

Dapper will open and close the connection for you, parameterize your SQL query, and then it will hydrate the Customer object’s FirstName, LastName, 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_Name, L_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):

 

 

 


public class Customer
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string ZipCode { get; set; }
}
public List<Customer> GetCustomersByState(string state)
{
var dbConnection = new SqlConnection("SomeConnectionString");
string sql = @"SELECT F_Name AS FirstName,
L_Name AS LastName,
Zip_Code AS ZipCode
FROM Customers
WHERE State = @State";
var parameters = new DynamicParameters();
parameters.Add("State", state);
return dbConnection.Query<Customer>(sql, parameters).ToList();
}

view raw

CustomerQueriesAliases.cs

hosted with ❤ by GitHub

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:

 

 

 

 


public class Customer
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string ZipCode9 { get; set; }
}
public List<Customer> GetCustomersByState(string state)
{
var dbConnection = new SqlConnection("SomeConnectionString");
string sql = $@"SELECT F_Name AS {nameof(Customer.FirstName)},
L_Name AS {nameof(Customer.LastName)},
Zip_Code AS {nameof(Customer.ZipCode9)}
FROM Customers
WHERE State = @State";
var parameters = new DynamicParameters();
parameters.Add("State", state);
return dbConnection.Query<Customer>(sql, parameters).ToList();
}

view raw

CustomerQueriesWithNameof.cs

hosted with ❤ by GitHub

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”

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

    Reply

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

    Reply

Leave a Reply