I’ve always enjoyed the concept behind LINQ: write SQL-esque queries directly in your code, with the compiler able to provide syntactical and type checks, and automatically generate the correct code to do what you wanted.
Unfortunately, there’s nothing like that available for Delphi. There could be, if the team would shift their focus to augmenting the language with actual useful features borrowed from managed languages, such as a proper extension method implementation, rather that burdening it with useless, counterproductive crap like pseudo-garbage collection and new String models. But for the time being, we’re out of luck.
But a while ago, an idea struck me. In .NET, LINQ uses anonymous methods to support expression class evaluation at runtime. But DWS is already an interpreted scripting system that operates on an expression tree! And since DWS has a language extension mechanism in place, I decided to poke around and see what was possible.
I came up with a simple proof of concept that could read very simplistic queries and generate SQL from them, then execute it using DWS’s database module and return a dataset. It required modifying both the compiler and the language extension system. So I sent it to Eric for feedback, just as an idea. I was expecting some constructive criticism, either explaining why it was a bad idea, or some things that would make it work better. I certainly never expected him to actually take it and put it in the DWS repository.
Guess what he did with my code?
So now I’ve been added as a contributor to the DWS project, so I can maintain this. I’ve been updating it since then, and now it will cover all of the basic syntax features (FROM, JOIN, WHERE, GROUP BY, ORDER BY, and SELECT). It’s still just a SQL generator, and not a full-blown LINQ implementation, which will take time, but if you’re working with DWS and database code, it already has a few advantages over the normal SQL model.
You can integrate parameters directly into your query, rather than having to put them someplace else. For example, consider this:
myDataset := myDatabase.Query(#' select VALUE1, dbFunction(VALUE2, :param1) from TABLE_1 where ID = :param2', [arg, userID]);
Having to move the params apart from the values you’re giving them makes it harder to read, especially on large queries, you can have DRY problems with it. What happens if you rearrange the query, you change the order of the params? What if you add or delete a param? You need to find the right place in your param array to insert/delete the new value.
This can be expressed in LINQ syntax more simply, without the problems noted above:
myDataset := from myDatabase.TABLE_1 where ID = userID select VALUE1, dbFunction(VALUE2, arg);
The dwsLinq parser can call back into the DWS parser to read Pascal expressions, and when building your query, it will automatically replace anything that came from a Pascal expression with a parameter, so you get the right SQL query.
This is still very much a work in progress, but if anyone would like to try it out, it’s in the repository now. Any bug reports would be welcome. Just keep in mind that this isn’t expected to be stable, or to work well, yet. It’s still mostly something I’m playing around with. But so far, it’s kinda cool.