Simon Harriyott

Tip for class and property names in SQL queries

A common pattern is using an ORM for writes, and SQL (e.g. via Dapper) for reads. A possible cause of bugs is having SQL in magic strings.

var query = "SELECT DISTINCT TeamName FROM InternatonalPlayers";

There may be a typo in a table or column name, which wouldn't be caught at compile time, or renaming something in the future that causes a database schema change could break the SQL.

To mitigate these problems, I've started using nameof in SQL statements:

var query = $"SELECT DISTINCT {nameof(InternationalPlayers.TeamName)} FROM {nameof(InternationalPlayers)}";

If there's a spelling mistake, the code won't compile. If either the class or property are renamed, the editor will update them automatically, or a compile error will occur.

Why you might not want to do this

Unit or integrating test will catch typos or renames in magic string SQL, but this will occur later than compile time.

It makes the SQL longer. In the example above, you could create a teamName variable for the long property name, which will add another line of code (and possibly blank line for clarity).

Foreign key Id columns may have an _id or Id suffix added by the ORM, which don't appear in the property names. You could add this into the SQL directly after the nameof closing curly brace, but then we've introduced magical stringyness. We could look this up from the ORM configuration, but I'm sure you'll posit that this is going a bit far.

Anyway, there we are. Take it or leave it. HTH.

29 September 2017