In the <a href="{app}/view_article.php?id=2985">first installment</a>, we covered the basics of mixing custom SQL with ORM-generated queries. We also took a look at a solution that uses direct ADO database access to perform arbitrarily complex queries. In this installment, we will see more elegant techniques that make use of the <c>CustomCommandText</c> property of Quino queries. We'll approach the desired solution in steps, proceeding from attempt #1 -- attempt #<a href="" source="" author=""></a>5. <abbr title="Too long; didn't read">tl;dr</abbr>: Skip to attempt #5 to see the final result without learning <i>why</i> it's correct. <h>Attempt #1: Replacing the entire query with custom SQL</h> An application can assign the <c>CustomCommandText</c> property of any Quino query to override some of the generated SQL. In the example below, we override <i>all</i> of the text, so that Quino doesn't generate any SQL at all. Instead, Quino is only responsible for sending the request to the database and materializing the objects based on the results. <code> [Test] public void TestExecuteCustomCommand() { var people = Session.GetList<person>(); <hl>people.Query.CustomCommandText = new CustomCommandText { Text = @" SELECT ALL ""punchclock__person"".""id"", ""punchclock__person"".""companyid"", ""punchclock__person"".""contactid"", ""punchclock__person"".""customerid"", ""punchclock__person"".""initials"", ""punchclock__person"".""firstname"", ""punchclock__person"".""lastname"", ""punchclock__person"".""genderid"", ""punchclock__person"".""telephone"", ""punchclock__person"".""active"", ""punchclock__person"".""isemployee"", ""punchclock__person"".""birthdate"", ""punchclock__person"".""salary"" FROM punchclock__person WHERE lastname = 'Rogers'" };</hl> Assert.That(people.Count, Is.EqualTo(9)); } </code> This example solves two of the three problems outlined above: <ul> It uses only a single query. It will work with a remote application server (although it makes assumptions about the kind of SQL expected by the backing database on that server). But it is even more fragile than the previous example as far as hard-coded SQL goes. You'll note that the fields expected by the object-materializer have to be explicitly included <i>in the correct order</i>. </ul> Let's see if we can address the third issue by getting Quino to format the <c>SELECT</c> clause for us. <h>Attempt #2: Generating the <c>SELECT</c> clause</h> The following example uses the <c>AccessToolkit</c> of the <c>IQueryableDatabase</c> to format the list of properties obtained from the metadata for a <c>Person</c>. The application no longer makes assumptions about which properties are included in the select statement, what order they should be in or how to format them for the SQL expected by the database. <code> [Test] public virtual void TestExecuteCustomCommandWithStandardSelect() { var people = Session.GetList<person>(); <hl>var accessToolkit = DefaultDatabase.AccessToolkit; var properties = Person.Metadata.DefaultLoadGroup.Properties; var fields = properties.Select(accessToolkit.GetField);</hl> people.Query.CustomCommandText = new CustomCommandText { Text = <hl>string.Format( @"SELECT ALL {0} FROM punchclock__person WHERE lastname = 'Rogers'", fields.FlattenToString() )</hl> }; Assert.That(people.Count, Is.EqualTo(9)); } </code> This example fixes the problem with the previous one but introduces a new problem: it no longer works with a remote application because it assumes that the client-side driver is a database with an <c>AccessToolkit</c>. The next example addresses this problem. <h>Attempt #3: Using a hard-coded <c>AccessToolkit</c></h> The version below uses a hard-coded <c>AccessToolkit</c> so that it doesn't rely on the external data driver being a direct ADO database. It still makes an assumption about the database on the server but that is usually quite acceptable because the backing database for most applications rarely changes.<fn> <code> [Test] public void TestCustomCommandWithPostgreSqlSelect() { var people = Session.GetList<person>(); var accessToolkit = <hl>new PostgreSqlMetaDatabase().AccessToolkit;</hl> var properties = Person.Metadata.DefaultLoadGroup.Properties; var fields = properties.Select(accessToolkit.GetField); people.Query.CustomCommandText = new CustomCommandText { Text = string.Format( @"SELECT ALL {0} FROM punchclock__person WHERE lastname = 'Rogers'", fields.FlattenToString() ) }; Assert.That(people.Count, Is.EqualTo(9)); } </code> We now have a version that satisfies all three conditions to a large degree. The application uses only a single query and the query works with both local databases and remoting servers. It still makes some assumptions about database-schema names (e.g. "punchclock__person" and "lastname"). Let's see if we can clean up some of these as well. <h>Attempt #4: Replacing only the <c>where</c> clause</h> Instead of replacing the entire query text, an application can replace individual sections of the query, letting Quino fill in the rest of the query with its standard generated SQL. An application can append or prepend text to the generated SQL or replace it entirely. Because the condition for our query is so simple, the example below replaces the entire <c>WHERE</c> clause instead of adding to it. <code> [Test] public void TestCustomWhereExecution() { var people = Session.GetList<person>(); people.Query.CustomCommandText = new CustomCommandText<hl>(); people.Query.CustomCommandText.SetSection( CommandTextSections.Where, CommandTextAction.Replace, "lastname = 'Rogers'" );</hl> Assert.That(people.Count, Is.EqualTo(9)); } </code> That's much nicer---still not perfect, but nice. The only remaining quibble is that the identifier <c>lastname</c> is <i>still</i> hard-coded. If the model changes in a way where that property is renamed or removed, this code will continue to compile but will fail at run-time. This is a not insignificant problem if your application ends up using these kinds of queries throughout its business logic. <h>Attempt #5: Replacing the <c>where</c> clause with generated field names</h> In order to fix this query and have a completely generic query that fails to compile should anything at all change in the model, we can mix in the technique that we used in attempts #2 and #3: using the <c>AccessToolkit</c> to format fields for SQL. To make the query 100% statically checked, we'll also use the generated metadata---<c>LastName</c>---to indicate which property we want to format as SQL. <code> [Test] public void TestCustomWhereExecution() { var people = Session.GetList<person>(); <hl>var accessToolkit = new PostgreSqlMetaDatabase().AccessToolkit; var lastNameField = accessToolkit.GetField(Person.MetaProperties.LastName);</hl> people.Query.CustomCommandText = new CustomCommandText(); people.Query.CustomCommandText.SetSection( CommandTextSections.Where, CommandTextAction.Replace, <hl>string.Format("{0} = </hl>'Rogers'"<hl>, lastNameField)</hl> ); Assert.That(people.Count, Is.EqualTo(9)); } </code> The query above satisfies all of the conditions we outlined above. it's clear that the condition is quite simple and that real-world business logic will likely be much more complex. For those situations, the best approach is to fall back to using the direct ADO approach mixed with using Quino facilities like the <c>AccessToolkit</c> as much as possible to create a fully customized SQL text. <n>Many thanks to <a href="http://blogs.encodo.ch/news/view_user.php?name=Urs">Urs</a> for proofreading and suggestions on overall structure.</n> <hr> <ft>If an application needs to be totally database-agnostic, then it will need to do some extra legwork that we won't cover in this post.</ft>