LinqPad DocumentDb (now CosmosDb) driver

Akos Nagy -

If you are a .NET programmer, you are probably familiar with LinqPad. I myself find it useful in some situations (and there are others, when not) and I do use it myself.

For a while I've been looking for a new project that I could hack on in my spare time (which I don't really have...). Here's the cool thing - LinqPad is extensible with drivers. Drivers are basically dlls that allow you to connect to a variety of data sources from LinqPad and run Linq queries against them. Again, not going into the full story (you can read the docs, if you are really deeply interested).

Also, I like Azure DocumentDb (technically now it's CosmosDb, but not even Microsoft has finished with the rebranding, so I'll stick with DocumentDb for this post). And what do you know, DocumentDb is a data source, which has no LinqPad support yet. And with that realization, my project began.

And now, without further ado:

LinqPad DocumentDb Driver

So I went through the documentation (and asked a couple of questions on SO), and then wrote the driver. You can check out the source code on Github, and download the binaries from here.

Here's a brief overview of the features (actually, not so brief, because I think it's awesome):

Static driver

A static driver uses a prebuilt dll that you have to load to discover the data source and run the queries. It's less fun the work with, but it was a good start for me to get to know how the system works and how should I write the more advanced dynamic library.
To help you get started, I created a class library called ContextLibrary, which is up in the Github repo as well (or you can download the binary from here). You can use the base classes and methods in this class library to build your static driver data context, load it into LinqPad and get querying. I included some samples on how to use it in the repo.

Dynamic driver

A dynamic driver is a driver where you don't have to load any dlls for your context to be created. It is the responsibility of the driver to discover the data source, build the "schemas" (whatever they might mean in that perticular case) and provide a way to interact with the discovered items.

This is all good and shiny for a relational database, but in the case of DocumentDb, there is no schema or structure to be discovered (aside from the the existence of collections, sps and udfs). So a dynamic driver might not make much sense in this case.

But implementing simply a static driver makes no sense either. First off, there is really not many challenges in that, and second, nobody wants to write and build a dll before they can use LinqPad (that's the whole point — not having to start Visual Studio). A static driver is good if you already have your context class (like the DbContext descendant in case of Entity Framework), but not for educating or learning.

So I did implement a dynamic driver. It works fine, but the lack of schema made some sacrifices necessary. The most important one being the fact the everything runs on the client side. If you create a query against a collection, the contents of the collection are downloaded and then you are essentially making Linq-to-objects queries.

The problem comes from the fact that in order to support a schemaless structure, dynamic have to be used. But Linq and dynamic don't really mix well (expression trees cannot contain dynamic references) so I had to get creative and this is what I came up with. Go ahead and check out the source code; if you have a better idea, suggestions and PRs are welcome.

Querying collections with Linq

If you load up the driver and connect to a data source, the driver automatically discovers the collections that are available and you can run queries against them:

If you use the dynamic driver, as mentioned before, you are basically running Linq-to-objects queries against the downloaded contents of the collection. If you use the static driver, then the queries you create are executed on the server-side.

As a bonus, if you right-click on a collection, you have all sorts of shortcuts to execute queries:

Stored procedures and user-defined functions

Both the static and the dynamic drivers support stored procedures and user defined functions (though a little differently).

First off, they are listed in the browser window:

Second, you can run an sp if you want either by typing the call manually, or right-clicking the sp name in the browser and importing the call into the editor window. The results are then displayed in a table form:

Parameters are also supported.

Since the results of the sp are loaded into memory, you can go ahead and use Linq-to-objects to query your data.

If you use the dynamic driver, you don't really need to do anything. If you use the static driver, you have to create a method with the name of the SP that returns IQueryable (T is your type that you are querying) and inside the method call the CreateStoredProcedure() method (you can check out the Github sample for more).
UDFs are also supported — you can run them as an SQL query (see below).

And the nice addition: if you hover over an sp or udf, you can check out their code:

SQL support

Here's the coolest of all: if you change your language to SQL, you can run DocumentDB sql queries directly from LinqPad and see the results in the table.

The syntax is almost entirely coherent with that of DocumentDb. The only difference is that you have to first specify the collection, against which you'd like to run the query (note that cross-collection queries are not supported, so this is not really a problem).

Check out the Github repo — yes, I have really implemented an ADO.NET provider for DocumentDb for this to work (well, not all of it, but it's a good start).

Planned features

Of course, the work is never done. Here's a couple of features that I would like to add in the future (if you want to help, feel free to submit a PR on Github):

  • Nicer connect dialog: The current connect dialog is a little rudimentary. That's mostly because I'm a back-end guy and while I'm not really a fan of the hypstering everything from the command line, I don't really care about the UI. But it would be nice if you could just connect to your Azure subscription and then select the data source from a treeview or something.
  • Populating the SQL window: DocumentDb Linq queries are compiled to data source specific queries, just like in the case of Linq-to-Entities or every other Linq-based query API (if you create a query with the SDK and call ToString() on it, you can actually see the JSON payload that contains the query). LinqPad has a tab to display the generated data source specific query from the Linq query — hooking this up would be a nice addition.
  • Trying to find ways the push even more things to the server side. I'm not sure how much of this is possible, but this is part of a continuous improvement process (not that critical though — my experience is people usually only use LinqPad for fiddling, and for that convenience is a more important factor than performance).
  • Maybe the ADO.NET provider could be extended to support more features, not just the ones that LinqPad currently uses. That might be a good next step.

Disclaimer and license notes

I have to put this in here, because I kinda have to. As for the driver, it is released under MIT licensing. Feel free to use it, but only at your own risk. If you liked it, leave a shout-out in the comments.

Icons made by Freepik from www.flaticon.com is licensed by CC 3.0 BY

Also see the source code for every indication of reasonable attribution.

I would like to thank all the people that helped me when I got stuck with the driver. You can follow the chronicle of my development in these SO posts: