Constructing Type-Safe SQL Statements from ORM Classes

Suppose you have a database application, and the database tables are mapped onto C# classes as used by an ORM such as Entity Framework or NHibernate.

Suppose you need to construct an SQL statement manually, because your ORM does not support or implement (or interface to) a given SQL feature.

Of course, you can always write the SQL statement manually, and query data using EF’s Database.SqlQuery() or NHibernate’s CreateSQLQuery().

The problem I found is that as soon as the data model changes, these manually crafted SQL statements are bound to fail.

Let’s have a look at a simple SELECT statement involving a JOIN of two tables (of course ORMs manage such a query, this is just an illustration):

SELECT s.Name AS SupplierName, a.ZipCode AS AddressZipCode, 
       a.City AS AddressCity, a.Street AS AddressStreet
FROM  Supplier s
INNER JOIN Address a ON s.AddressId = a.Id

The statement includes table names, table aliases, table column names, and column aliases, and I want to construct the column names for the SQL statement from the ORM’s mapped classes.

Gladly I already have the GetPropertyName() function to retrieve as class’s property names, so we can focus on enumerating them:

public static string EnumerateColumns<T>(
    string tablePrefix, 
    string columnPrefix, 
    params Expression<Func<T, object>>[] columns)
{
    return string.Join(", ", 
        columns.Select(c =>
            (tablePrefix != null ? (tablePrefix + ".") : "") +
            GetPropertyName(c) +
            (columnPrefix != null 
                ? (" AS " + columnPrefix + GetPropertyName(c)) 
                : "")));
}

So we have a function handling optional table aliases and optional column name prefixes, which we can invoke for every joined table of our statement:

var sql = "SELECT "
    + EnumerateColumns<Supplier>("s", "Supplier", s => s.Id, s => s.Name)
    + ", "
    + EnumerateColumns<Address>("a", "Address", 
        a => a.ZipCode, a => a.City, a => a.Street)
+ @"
FROM " + nameof(Supplier) + @" s
INNER JOIN " + nameof(Address) + " a ON s." 
    + nameof(Supplier.AddressId) + " = a." + nameof(Address.Id);

Console.WriteLine(sql);

There is a little bit of cheating hidden in this code, assuming that table names match their class names, and column names match their property names. If they names differ, you can mark up the class declaration with some attribute and query it using GetCustomAttributes(), or EF’s pluralization service (if it is used).

The full code for this article available on my GitHub.