A while back I created a CosmosDb data context driver for LinqPad. Originally, I found the request on uservoice and I thought I'd give it a try.

The end result was pretty OK, I guess. People started using it; I mean, this is not the "next big thing", but it was fun to write and if some of you can use it, that's even better :)

A little while ago I even got a feature request on Github: how cool would it be if the driver would support a scenario where you could issue raw SQL queries from the C# window? You can already issue raw SQL requests (that was one of the best and most fun to implement features), but it would be even better if you could use the C# window to execute SQL queries and then LInQ2Objects to fiddle with the results further (and currently only LInQ2Objects, because the DocumentClient does not support a query that is a mixture of raw SQL and LInQ operators). Check out the issue for more details.

However, this feature made my original, rather simple and not-architected solution unusable. Long story short, I had to come up with a new architecture and design and implement a completely new "back-end" for the driver.

This, in turn, turned out to be a library that can be used on its own to access data in CosmosDb, so I think it's worth looking at it by itself.

CosmosDbContext and CosmosDbCollection

I took some inspiration from Entity Framework and created a class called CosmosDbContext. This represents a connection to a database. You have to inherit this class and then you have to create properties of type CosmosDbCollection<T> in the context to access the collections:

public class VehicleContext : CosmosDbContext.CosmosDbContext
{
  public VehicleContext() : base("https://localhost:8081", "C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==", "VehicleDb") { }

  [CosmosDbCollection(CollectionName = "vehicles")]
  public CosmosDbCollection<Vehicle> Vehicles { get; set; }

}

You have to use an attribute that I created for this feature to indicate that this is indeed a collection. If you want, you can specify a collection name too (by default, the name of the property is used for the collection name).

And then, you can use this context to access the data in the database:

using (var ctx = new VehicleContext())
{
  var d = ctx.Vehicles.Where( v=> v.LicensePlate=="123-ABC" ).ToList();
}

In order for this to support creating LInQ queries, I had to implement a custom LInQ-provider. You can find the details in the repo (link at the bottom of the post).

Supporting raw SQL queries

The introduction of my new CosmosDbCollection<T> type made it possible to introduce new features just for this type (without this, my only other option would have been to create extension methods for IQueryable<T>, and that's just not nice). One such feature is executing raw SQL queries against a collection:

using (var ctx = new VehicleContext())
{
  var queryString = "SELECT * FROM root WHERE root.licenseplate = \"ABC-123\")";
  var d = ctx.Vehicles.ExecuteSql<Vehicle>(queryString);
}

Nice. Now you can add additional LInQ operators to the end of your method call, because ExecuteSql returns an IEnumerable<T>. The only drawback is that any additional LInQ operations are executed client side, but that's a limitation of the DocumentClient that I mentioned above.

Supporting FeedOptions

While you can definitely live without it if you only want to fiddle, a nice addition of my approach is that now you can shape the execution of your queries using the FeedOptions that are available in the DocumentClient API. You can set the properties per collection and they will be used when a query is executed:

using (var ctx = new VehicleContext())
{
  ctx.Vehicles.Options.EnableCrossPartitionQuery = true;
  var d = ctx.Vehicles.Where( v=> v.LicensePlate=="123-ABC").ToList();
}

Supporting SQL query logging

A nice feature of the EF DbContext is that it has event that you can subscribe to, and every time a query is executed this event is called and the translated SQL is passed to the event as a parameter.

This new approach made this possible too — the only problem is that the public API of DocumentClient does not support this. So I went rouge, looked at the source code using a decompiler, found the right piece of code and finally used reflection to get to the translator. It's ugly as hell, but I mean, this is a tool for developers, so I guess this one time only it might fly. If you are interested, here's the little piece of code that does the translation:

private string GetQueryTextUNSAFE(Expression newExpressionTree)
{
  try
  {
    var translator = client.GetType().Assembly.GetType("Microsoft.Azure.Documents.Linq.DocumentQueryEvaluator");
    var translatorMethod = translator.GetMethod("Evaluate", System.Reflection.BindingFlags.Static | System.Reflection.BindingFlags.Public);
    var result = translatorMethod.Invoke(null, new[] { newExpressionTree });
    if (result == null)
      return string.Empty;
      
    var queryText = (string)result.GetType().GetProperty("QueryText").GetValue(result);
    return queryText;
  }
  catch (Exception ex)
  {
    return $"Could not get query text: {ex.ToString()}";
  }
}

You can simply subscribe to the event and execute a query:

using (var ctx = new VehicleContext())
{
  ctx.Log += Console.WriteLine;
  var d = ctx.Vehicles.Where( v=> v.LicensePlate=="123-ABC").ToList();
}

And then you get the translated SQL:

Nice :)

Support for untyped data

Obviously, since CosmosDb is a document-store, there might be scenarios where you cannot write completely typed queries. The old version of the LinqPad driver had support for this and I intended to keep it in the new version. The untyped collections are supported using the dynamic language feature of C# and the ICosmosDbCollection<T> interface:

public class VehicleContext : CosmosDbContext.CosmosDbContext
{
  public VehicleContext() : base("https://localhost:8081", "C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==", "VehicleDb") { }

  [CosmosDbCollection(CollectionName = "vehicles")]
  public ICosmosDbCollection<dyanmic> Vehicles { get; set; }

}

This supports everything that the typed version (the options and the raw SQL). The only problem is that since it's dynamic, it is again impossible to create actual IQueryable<T> in C# based on this source (an expression tree cannot contain dynamic elements). So this again queries the whole collection server-side, returns an IEnumerable, and then you can use plain old LInQ2Objects to do anything else on the client side. It's not ideal, but good enough for fiddling (and I wouldn't write untyped, dynamic code in production, so that's fine).

Support for stored procedures

Stored procedures are also supported, just like in the previous version; you can use methods from the base context class to execute a stored procedure and using this, you can create wrapper methods:

public class VehicleContext : CosmosDbContext.CosmosDbContext
{
  public VehicleContext() : base("https://localhost:8081", "C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==", "VehicleDb") { }
       

  public IEnumerable<Vehicle> GetVehicles()
  {
    var result = base.ExecuteStoredProcedure<Vehicle>("mySpecialSp", "Vehicles");
    return result;
  }
  
  public IEnumerable<dynamic> GetVehiclesUntyped()
  {
    var result = base.ExecuteDynamicStoredProcedure<dynamic>("mySpecialSp", "Vehicles");
    return result;
  }
}

This feature was available in the last version as well, see the original post about it.

Closing arguments

So that's that. You can download the whole thing from Github and use it if you'd like to. My next priority is to integrate this context library to LinqPad and publish a 2.0 version, hopefully soon.

Also note that Microsoft is working on an EF Core provider for CosmosDb, that will essentially be the same as my little context, only a thousand times better, I imagine. They are not clear on the timetable, but "soon" is the magic word they have been using for a while now. I wonder if this year's Build conference might be a good time to announce this... Anyway, of course if an official EF provider comes out, you should use that (I mean, if you can; I'm not sure how or if that provider could be used from full .NET code), but until then, this might be a good option. And of course, stay tuned for the upgraded version of the LinqPad driver with these cool new features!