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)
(not to be confused with LINQ to SQL from Microsoft)
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);
}
}
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);
}
/* Models\Post.LinkedQuestions.cs@105 */
select top (@top)
p.Id
, p.Title
, p.Score