I'm using Dapper in my project. Very often there are situations when I need to change the SQL query depending on some variables.
For example:
public override QueryResult Handle(Query query)
{
var sql = "SELECT field FROM table";
if(someModel.IncludeSomething){
// add inner join something + add SELECT something.field
}
if(conditionN){...}
return Connection.Query<QueryResult>(sql, query);
}
How do you work with dynamic SQL queries in your projects?
I had the same situation, to develop custom dashboards/reports with dynamic filters, grouping, and sometime with the need to join with other tables.
Since the Stored Procedures approach don't play nicely in such case, I have developed my own library SqlKata, a dynamic query builder that offer the above requirements, without sacrificing with the security and performance, since it uses the Parameters Binding technique. check it out and hope you will find it helpful :)
Leaving aside the performance issue (which may or may not be), I use several approaches depending on what's the simplest yet maintainable.
If you need to create queries involving joins dynamically, it might be a hint of improper design and a strong maintenance smell. I would say to think it again and find a way to refactor it.
Personally, I didn't encounter a problem that couldn't be solved with options 1-2, but maybe your reporting requirements are way more complex than mine.
I do my best not to dynamically construct SQL queries. I have come aboard projects several times that do this, and it has always been a source of trouble. Compared to stored procedures, dynamically constructed SQL will incur query parsing and planning costs much more often, will incur higher data transmission costs always, and are much more likely to result in unforeseen, unwieldy, unperformant, and otherwise problematic queries. Basically, this approach is guaranteed not to scale.
Instead, if at all possible, maintain a set of tuned stored procedures that collectively cover your query space. In some cases, the applicable stored procedure will be more general than you require (e.g. have a "WHERE [Foo] = @foo OR @foo IS NULL" clause that you could have left off entirely if you knew that the query was never going to match on Foo). Either accept that cost or write a specialized stored procedure for situations when the more general stored procedure becomes a bottleneck.
If you ignore my advice and do construct SQL dynamically, then at very least use parameters instead of values wherever you can. If you construct "WHERE [Foo] = 1" for one query and "WHERE [Foo] = 2" for another, both queries will be compiled separately. If instead you construct "WHERE [Foo] = @foo" for both and pass @foo = 1 for the first and @foo = 2 for the second, then you will at least just incur the compile cost once and the cached query plan will be used for the second call.
