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 Query(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(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
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.