Code of the example: https://github.com/leandro-hl/design-patterns
Have you ever seen SQL queries made in a programming language like:
I saw it in some old systems, concatenating the beautiful SQL query through the entire system, module to module, each one adding its little piece of logic, creating a huge monster.
Another approach I’ve seen was calling a SP (Stored Procedure) in SQL from the DAL (Data Access Layer). It seems a little more beautiful, at least you don’t have a huge string created through lots of modules, but then, when you go to the SP you’d find something like
And this is just a little insert, not a big deal here. Another one to take in mind, you don’t have an automated test for it so, enjoy your creepy debug session!
After years the ORM’s (Object Relational Mapping) frameworks have come. They have their own goods and problems but, at least, they allow us to write this article about creepy data oriented programming and QO (Query Objects).
The Query Object Pattern definition shows that it’ll be used to create SQL statements like the first one showed above, but I don’t use it on that way thus I’m gonna skip it.
So, what is a QO? The definition is pretty simple: “An object that represents a database query”.
There’re different implementations around the web and also you can create your own thinking on the needs of your project.
On this post we use two approaches. Each one involve a Query Object and a little Repository with some dummy data.
Both QO share the same interface to set the filters to apply.
Each QO has to return itself to allow you to use Fluent programming to chain filters. So, to achieve it and also put all the filters in one place, I made a generic interface that receives each QO interface.
The First Approach
On this one, the QO has a Criteria method, which defines the logic to apply. We pass it as a parameter to the Repository and filter the collection. The QO interface will look like:
Where the IExchangeCriteria define the mentioned method.
As we are working with C# we can make use of Lambda expressions. Thus, our method could return a Func object which is just a function that receive an Exchange as a parameter and returns a boolean true if the evaluated one fit the filters. It’s showed below:
That kind of filters is pretty common to use. It allows you to make dynamic queries in one place. You’ll see it in a Stored Procedure like
Of course you’re not going to compare the dates on this way probably but it illustrates the example.
Coming back to OOP, our repository has a Get method that receives an ICriteria and applies the filter in a Lambda’s Where clause
So, a way to use it would be:
Now we can use any QO that we want just implementing creating a class that implement IExchangeCriteria.
The Second Approach
The second design that you’d use injects the Repository inside the QO, adding an ApplyFilters method. Our new interface is IExchangeQueryObject.
As you’ll see the method returns the result of the query and not the Criteria to be applied.
Doing so, our next QO would be like:
The main here is to concatenate Where clauses per each possible filter and execute the query at the end (the ToList method). To do it we set two variables when we call one of the ForFilterXYZ(). One is the filter itself and the other is the boolean used to select the clauses to concatenate.
These are the approaches that I’ve seen in my life, working for old systems and new ones. I think that, at this point, you’ve seen almost 20 years of software history!
By the way, have you used any of these approaches? Do you use another one?
Thanks for reading!
Bibliography
- Patterns of Enterprise Application Architecture By Martin Fowler, David Rice, Matthew Foemmel, Edward Hieatt, Robert Mee, Randy Stafford
