On: StackOverflow Update: 560M Pageviews a Month, 25 S...

[pbz]

Dapper is nice, but if you want to have the compiler check your queries, and still run fast, I recommend LINQ to DB: https://github.com/linq2db/linq2db

(not to be confused with LINQ to SQL from Microsoft)

[Nick-Craver → pbz]

To be fair, the compiler is checking against what you told it the database looks like. This can help with lots of things, but still has the fundamental disconnect problem you'll get at runtime with either approach.

A huge problem with the tradeoff we had (well, still have in some areas) is the generated SQL is nasty, and finding the original code it came from is often non-trivial. Lack of ability to hint queries, control parameterization, etc. is also a big issue when trying to optimize queries. For example we (and by "we" I mean, "I made Marc Gravell do it") added literal replacement to Dapper to help with query parameterization which allows you to use things like filtered indexes. In dapper we also intercept the SQL calls to dapper and add add exactly where it came from. Here's what that looks like, replicate for the other methods:

    public static IEnumerable<T> Query<T>(this DataContext db, SqlBuilder.Template template, bool buffered = true, int? commandTimeout = null, IDbTransaction transaction = null, [CallerFilePath]string fromFile = null, [CallerLineNumber]int onLine = 0, string comment = null)
    {
        using (db.Connection.EnsureOpen())
        {
            return SqlMapper.Query<T>(db.Connection, MarkSqlString(template.RawSql, fromFile, onLine, comment), template.Parameters as object, transaction ?? db.Transaction, buffered, commandTimeout);
        }
    }

And here's the our marking method tailored for our code, but you get the idea:

    private static string MarkSqlString(string sql, string path, int lineNumber, string comment)
    {
        if (path.IsNullOrEmpty() || lineNumber == 0)
        {
            return sql;
        }

        var commentWrap = " ";
        var i = sql.IndexOf(Environment.NewLine);

        // if we didn't find \n, or it was the very end, go to the first space method
        if (i < 0 || i == sql.Length - 1)
        {
            i = sql.IndexOf(' ');
            commentWrap = Environment.NewLine;
        }

        if (i < 0) return sql;

        // Grab one directory and the file name worth of the path
        //   this dodges problems with the build server using temp dirs
        //   but also gives us enough info to uniquely identify a queries location
        var split = path.LastIndexOf('\\') - 1;
        if (split < 0) return sql;
        split = path.LastIndexOf('\\', split);

        if (split < 0) return sql;

        split++; // just for Craver

        var sqlComment = " /* " + path.Substring(split) + "@" + lineNumber + (comment.HasValue() ? " - " + comment : "") + " */" + commentWrap;

        return sql.Substring(0, i) +
               sqlComment +
               sql.Substring(i);
    }

This results in a comment at the top of the query like this:

    /* Models\Post.LinkedQuestions.cs@105 */ 
    select top (@top) 
           p.Id 
         , p.Title
         , p.Score

It's such a simple approach using the caller member attributes, but it saves so much time tracking things down.

[mistermann → Nick-Craver]

I'm curious if the comments above exist in the source code, or have been added here for the benefit of readers on HN?

[kmontrose → mistermann]

That's a straight copy/paste from the Stack Overflow codebase (Helpers\SqlMapper.cs:332 at time of writing, if you care). Now the typos and snark are out there for everyone to see.