SQL code reuse with C#

Question

I have some code that allows for SQL reuse for C#. I'm pretty happy with it, but the fact that I can't find anything similar on the web makes me think I'm going about it the wrong way. I have a GitHub repo with a Visual Studio solution, and the code is repeated down below.

Here's the use case:

I'm working on a fairly standard C# and SQL Server project where we're generating reports. Each report is essentially the results of SQL query, displayed in HTML in the browser. I'd rather not use an ORM like Entity Framework, since we need to exactly control the SQL for performance reasons (e.g. use SQL server windowing functions, table hints, etc).

However, many of the reports are similar, just slicing and dicing by different attributes, so there's a desire for code reuse. SQL Server has stored procedures, views, and functions, but the consensus seems to be that you'll get into trouble trying to use them to create abstractions. To get around this I'm trying to use C# to reuse SQL code.

I'm essentially using Visual Studio's T4 text templates to generate SQL files at development time. I'm then passing the SQL to Stack Exchange's Dapper.NET project to handle variable substitution, issuing the query, and POCO [de]serialization.

Given a query like this, in the template file CuteAnimalsByLocation.tt:

<#@ output extension=".sql" #>
select * from animals a
where a.IsCute = 1 and a.IsFuzzy = 1 and a.Location = @location

And this file, DeadlyMachinesByLocation.tt:

<#@ output extension=".sql" #>
select * from DeadlyMachines m
where m.IsLethal = 1 and m.HasExplosives = 1 and m.Location = @location

I can reuse the above queries in a more complex query by writing the following, AnimalsInPeril.tt:

<#@ output extension=".sql" #>

WITH 
CuteAnimalsInLocation as 
( 
    <#@ include file="CuteAnimalsByLocation.tt" #>
),
DeadlyMachinesInLocation as
(
    <#@ include file="DeadlyMachinesByLocation.tt" #>
)
select a.* from CuteAnimalsInLocation a
inner join DeadlyMachinesInLocation m on a.Location = m.Location

I can run all 3 of these queries like this: (assuming the POCOs Animal and DeadlyMachine exist; they're just POCOs that map to the table schemas):

using (var connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    var queryParams = new { Location = "NorthAmerica" };

    IEnumerable<Animal> animalsNeedingHelp = 
        connection.QueryFromFile<Animal>("AnimalsInPeril", queryParams);
    IEnumerable<Animal> cuteAnimals = 
        connection.QueryFromFile<Animal>("CuteAnimalsByLocation", queryParams);
    IEnumerable<DeadlyMachine> deadlyMachines = 
        connection.QueryFromFile<DeadlyMachine>("DeadlyMachinesByLocation", queryParams);
}

connection.QueryFromFile is defined as this, and depends on Dapper:

public static class DapperFileExtensions
{
    public static IEnumerable<TReturn> QueryFromFile<TReturn>(this IDbConnection cnn, string file, object params = null)
    {
        var sql = File.ReadAllText(file + ".sql");
        return cnn.Query<TReturn>(sql, params);
    }
}

Can anyone see problems with this approach? I guess the only thing I can think of is that I could add caching around the File.ReadAllText call.

Answer 1

You say performance is a concern, but you're doing this.

select a.* from

That's poor form whether or not you already have a table scan in the underlying query plan for the CTE.

I think you misunderstood the article you linked to. It is warning against doing exactly what you've done here. SQL just doesn't lend itself to code reuse in the way a "regular" programmer is accustomed to. It's a different beast that way, it takes a different mindset.

SQL is a set based query language. Code reuse comes in the form of (well tuned) views and stored procedures. If you need this kind of fine grained control over the SQL, then keep it where it belongs, in the database.

Depending on just how dynamic your queries are, you may not always be getting the benefit of query plan caching. Each time a query that the analyzer hasn't seen before is processed, it has to generate a new plan, so you'll take a performance hit.

Lastly, I left a comment, but I should mention it here too. Be careful using query hints. You may be able to outwit the analyzer now, based on the current data, but the data will change over time. When the data has changed and your hint is no longer the most efficient query plan, the analyzer won't be able to choose the most efficient plan because you've told it not to. Performance gained this way may degrade over time.

Answer 2

The main problem I see with this approach is you have magical strings, which are not compile time safe.

I highly recommend you look into using an ORM like entity framework

public class AnimalsRepository : IDisposable
{
    private MyDataModel Model { get; set; }
    public AnimalsRepository()
    {
        Model = new MyDataModel();
    }
    public void Dispose()
    {
        if (this.Model != null)
            this.Model.Dispose();
    }
    public IQueryable<Animal> GetCuteAnimals(string location)
    {
        return this.Model.Animals.Where(a => a.IsCute && a.IsFuzzy && a.Location == location);
    }
    public IQueryable<DeadlyMachine> GetDeadlyMachines(string location)
    {
        return this.Model.DeadlyMachines.Where(m => m.IsLethal && m.HasExplosives && m.Location == location);
    }
    public IQueryable<Animal> GetAnimalsInPeril(string location)
    {
        return from a in this.GetCuteAnimals(location)
               join m in this.GetDeadlyMachines(location) on a.Location equals m.Location
               select a;

    }
}

See Nuget Package: https://www.nuget.org/packages/EntityFramework