In this edition: my team and I are embarking on a refactoring adventure, and there will be speed bumps along the way.
Plus: VS 2022 has a new Razor editor; can source generators be used as an attack vector?; and a smelly postscript to our Christmas tree saga.
Adventures in Dapper Refactoring
My team is currently in charge of a large, complex, relatively-old project which has been using Entity Framework 5 as their data persistence accessor (this is a .NET Framework 4.8 project, so we can't move to EF Core). I decided, after doing the numbers a few months ago, that it would benefit the project if we replaced EF5 with Dapper.
Most of the data access code was simple CRUD stuff, kinda like this:
In these kinds of circumstance, it would be pretty simple to move to a Dapper system, like so:
using(SqlConnection conn = new SqlConnection(MyConnectionString))
{
var users = conn.Query<User>("SELECT * FROM dbo.Users WHERE DateOfBirth > @now", new {now = DateTime.Now});
return users;
}
We even implemented a new, improved version of the DapperHelper object that I blogged about a while ago, which handles INSERT and UPDATE statements.
All of this seemed so simple and straightforward, as it often does in the beginning. But soon, issues were upon is.
Let's see an example. Below are three code snippets: a C# class, a SQL CREATE TABLE expression, and the C# code which is supposed to map the SQL table to the C# object. I've trimmed them down quite a bit to make the error more obvious. Can you see what's wrong?
public class Equipment
{
public int EquipmentID { get; set; }
public string Name { get; set; }
public int FileID { get; set; }
public DateTime OpenDate { get; set; }
//...
}
using(SqlConnection conn = new SqlConnection(MyConnectionString))
{
conn.Open();
var equipment = conn.Query<Equipment>("SELECT * FROM dbo.Equipment");
}
Yep, you got it: the property is named FileID but the SQL column is named FID. Which means we cannot use SELECT * in our queries, or at least, not in this way.
OK, you might think, no big deal. Let's just refactor the Dapper query to call the correct columns, and use AS to rename FID.
using(SqlConnection conn = new SqlConnection(MyConnectionString))
{
conn.Open();
var equipment = conn.Query<Equipment>("SELECT EquipmentID, Name, FID AS FileID, OpenDate FROM dbo.Equipment");
}
This isn't a huge deal with just one table, one column, and one property. I still though, still believed, that this refactoring wasn't gonna be too bad.
Now imagine doing this on a 220+ tables spread across four databases, 1000+ total properties, 150+ C# classes, with about 15% of them have this kind of naming issue, and any time that naming issue appears you have to enumerate ALL of the SQL columns. This is what we are faced with. Suddenly, refactoring to Dapper doesn't seem so nice anymore.
We're not stopping, though. In fact, we've put team rules into place that say things like "always enumerate the columns you want to get from SQL" and "be sure to check that the SQL column name and C# property name match". It's gonna be a long, trying journey, but I still believe the results will be worth it.
Obviously, whether or not it turns out to have been worth it, you will be the first to know, dear readers.
This article is for paying subscribers only
Sign up now and upgrade your account to read the article and get access to the full library of articles for paying subscribers only.