Skip to content

Using the Query class

Adam O'Neil edited this page Jul 21, 2019 · 8 revisions

Although there are well-known hazards of inline SQL, in the end, all things considered, I prefer inline SQL over Linq. And, to be clear, I love and use Linq often -- just not as a SQL replacement. That said, I do believe inline SQL in enterprise code should usually be isolated from an application's logic to keep application code concise, expressive, and testable for that matter. To accomplish that isolation, Postulate offers the Query<TResult> class, which is a wrapper around Dapper's query methods. I believe this is a good compromise between the productivity and power of inline SQL and the need to hide or abstract query complexity from calling services. The Query class lets you return strongly-typed results from inline SQL that is injection-proof -- while offering dynamic criteria. Use Query<TResult> any time you need a SELECT query in your application. Here's what a typical usage looks like in a hypothetical database:

public class MyQuery : Query<SomeResultClass>
{
    public MyQuery() : base ("SELECT * FROM [dbo].[SomeTable] {where} ORDER BY [SomeField]")
    {
    }
    
    [Where("[Search] LIKE '%'+@searchFor+'%'")]
    public string SearchFor { get; set; }

    [Where("[DateField]>=@fromDate")]
    public DateTime? FromDate { get; set; }

    [Where("[DateField]<=@toDate")]
    public DateTime? ToDate { get; set; }

    [Case(true, "[IsActive]=1")]
    [Case(false, "[IsActive]=0")]
    public bool? IsActive { get; set; }
}

Here's a breakdown of what's going on here:

  • The query's SQL is set in the constructor. Use {where} to indicate where dynamic criteria is inserted. Use {andWhere} instead if you have hard-coded criteria in your query that requires a joining AND.
  • Add properties to your query class that represent the criteria it accepts. If properties are optional, make them nullable and add a [Where] or [Case] attribute. The [Where] attribute defines the criteria that applies to a property. The [Case] attribute lets you associate multiple SQL expressions with a property based on the value of the property at runtime. The @parameter in your Where expressions should match the property name.
  • Use the [Phrase] attribute on a property to allow searching on several words at once. Words in quotes are queried together, and words with a leading hyphen are negated. Please have a look at tests for examples of what this is about.
  • Use the [Join] attribute on public bool properties of your query to include optional. Use {join} somewhere in the SQL of your query to indicate where optional joins are inserted.

Executing your query looks like this:

using (var cn = GetConnection())
{
    var results = new MyQuery() { Search = "whatever", IsActive = true }.Execute(cn);
}

What's happening?

  • You need an open database connection to execute your query. In my example, I have a fictional GetConnection method. You would need to adapt this for your application, and I recommend having some kind of convenient helper method for this.
  • Set any properties (criteria) for your query when you new up the MyQuery instance, then execute it with the open connection -- all one one line.

What about the result class?

My example returns IEnumerable<SomeResultClass>, but where is SomeResultClass defined? How would I get a C# class from SELECT *... or any other valid SQL? By convention, I create the result class in the same source file as the query class. I offer an open-source app for writing queries and generating C# result classes called Postulate Query Helper.

What about dynamic ORDER BY?

I don't have any built-in support for dynamic sorting, but my approach is to add a dictionary to my query class of arbitrary keys and SQL ORDER BY expressions. We don't want SQL being concatenated from user input, so the dictionary acts as a middle-man between the user input and raw SQL that forms the ORDER BY. Building on my prior example, this is how I would add a dynamic ORDER BY option:

public class MyQuery : Query<SomeResultClass>
{
    public MyQuery(string orderBy = "default") : base ($"SELECT * FROM [dbo].[SomeTable] {{where}} ORDER BY {OrderByOptions(orderBy)}")
    {
    }

    public static Dictionary<string, string> OrderByOptions
    {
        get
        {
            return new Dictionary<string, string>()
            {
                { "default", "[SomeField] ASC" },
                { "different", "[AnotherField] ASC" },
                { "yougetit", "[YetAnotherField] ASC, [AndAnother] DESC" }
            };
        }
    }
}

Now, MyQuery can be invoked with an optional constructor argument that sets the sort from one of three defined sort options. Make it even more convenient to use by using an enum as a key to your sort options dictionary instead of a string.

Testing Queries

Note as of 5/14/19, my testing approach has simplified a bit since writing this topic. For more updated testing examples, see Ginseng query tests.

Hazards of inline SQL can be mitigated with test automation. Postulate offers methodology and tools to help you integration test queries in your solutions. This is intended to verify that your SQL queries compile, not that they return specific results. On all your Query<TResult> types where you want test coverage, do the following:

  • Implement ITestableQuery. A helper method is provided to make this easy: TestExecuteHelper. For an example, see this query in a sample project. There are more examples in this project also.

  • Add a static method called GetTestCases that returns IEnumerable<ITestableQuery>. See this example. The point here is to return at least one instance of your query type. You can return multiple instances that test different parameter combinations.

Finally, in your unit test project, create the [TestMethod] method that will gather and execute all your test cases. An example is here. These are the key points of it:

  • Use the QueryTesting.GetTestCases static method to get all the test cases you've defined. This method requires an Assembly and the name of one of its referenced assemblies that contains ITestableQueries. The assembly argument should be Assembly.GetExecutingAssembly().

  • Open a database connection that can execute your queries, and loop through your test case queries, called TestExecute on each one. See example here. Any failures will show up in the Test Explorer with the name of the failing query. Note that TestExecute's only purpose is to call the query to verify that its SQL compiles correctly, not that it returns any particular results.

GetTestCases is offered so you don't have to remember to add a separate integration test for each query. The downside of this is that you have to read the failure error message pretty carefully to find a failing query. You can if you wish write separate tests for each query so that failures are easier to identify. In my opinion, I prefer the productivity of a single [TestMethod] that I don't have to maintain very much during the development of a project.

Here's an example error message of a failing query. I highlighted the query name to make it easy to see in this example.

img

Some follow-up notes on testing

I've run into trouble with QueryTesting.GetTestCases where it doesn't find my referenced project. Have a look at this example. The problem was that this line was failing because the project name wasn't found, even though my test project had a reference to it:

IEnumerable<ITestableQuery> queries = QueryTesting.GetTestCases(Assembly.GetExecutingAssembly(), "MvcSpace.Models");

I couldn't figure out the root issue, but I found a workaround in which I create a dummy variable in the test from that referenced project. This enables the GetTestCases method to work. I don't reference the dummy variable anywhere else in the test, and it has no bearing on the result.

You can’t perform that action at this time.