Work in progress: dwsLinq extension for DWS

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.

It could be done in DWS, but LINQ is a really complex system that makes heavy use of lambdas (aka anonymous methods,) which Eric Grange hasn’t gotten around to supporting yet outside of the JavaScript cross-compilation.  Apparently it’ll take a complete re-architecting of the execution model to make anonymous methods available.

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.

7 Comments

  1. Esteban Pacheco says:

    Great idea good job.

    Regarding your Delphi comment, reference count and unifying string models has lots to do with their long term projects. Crossplatform is their objective and maintaining a dozen of string types after the unicode implementation on multiple platforms is just messy. Same goes for reference counting on Android and iOS.

    It is not what you like, but personally, I’m more interested on other platforms than on having LINQ on Delphi.

  2. A. Bouchez says:

    I saw this LINQ extension to the DWS repository, and was impressed.

    We spoke about such integration some months ago with Eric, when he integrated the mORMot DB layer classes (SynDB* units) to its DWS dataset system.
    I’m quite sure he is very happy about your code proposal!
    Eric is very open minded.
    Which is great for any Open Source project.

    How do you handle naming collision? That is, if a SQL column has the same name as a local variable?

    • Mason Wheeler says:

      The dwsLinq parser gets column names by calling back into the DWS parser, and the DWS parser recursively calls back into dwsLinq when it finds an unknown name. This means that in case of a naming collision, the local variable will take precedence.

      However, if you have a problem like this, you can resolve it by using the table name too. There’s a special case in the dwsLinq parser where it treats identifier names like “x.y” as a single identifier. This won’t help if you have a Pascal variable in scope with the same name as the table, of course, but hopefully that won’t happen.

      • Eric says:

        To disambiguate you could recognize a special character like “&”, as I don’t think it’s used in SQL or Linq, and in Delphi/DWScript this can be used to explicitly state that what follows is a Pascal identifier and nor a keyword or something else (it allows to use “begin” as an identifier f.i), so it would naturally “work” in that case.

        • Mason Wheeler says:

          That’s an excellent idea. I just checked in an update that implements that: if the dwsLinq parser runs across an & in a place where it expects an expression or identifier, it will automatically treat whatever comes after it as a SQL identifier instead of calling back into the DWS parser.

  3. Brian frost says:

    Nice one Mason. I wondered what those units were for. More power to DWS.
    Would this work in my IDE demo allowing you to set breakpoints etc?

Leave a Reply to Eric