The Catch Block #85 - Adventures in Refactoring to Dapper

Welcome to the 85th edition of The Catch Block!

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:

using(MyContext context = new MyContext()
{
    var users = context.Users.Where(x => x.DateOfBirth < DateTime.Now);
    return users;
}

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.

The Catch Block #31 - Dapper Helper Beginnings

Let’s build a class that will generate parameterized SQL INSERT statements!

Exception Not FoundMatthew Jones

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; }
    //...
}

CREATE TABLE Equipment 
    (EquipmentID int, Name varchar(100), FID int, OpenDate datetime)

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.