Azure: SQL Data Services, Pt 3: IQueryProvider

Due to loss of old server, code is not available. Previous links are stripped until then.

A new library by the name of Omniscientist.Cloud.SqlServices.Linq has been created to be used in conjunction with the Omniscientist.Cloud.SqlServices library to allow the use of LINQ expressions with SQL Data Services entities. Basically a LINQ to Azure.

The SqlServices.Linq library provides implementations of IQueryable and IQueryProvider; I found creating the custom query provider to be very interesting, as there’s probably no better way to grasp the concepts of LINQ’s internals.

A gigantic benefit that we gain when using this custom IQueryProvider I created (OmniQueryProvider) is the ability to use LINQ expressions without having to waste performance. This is in contrast to doing a general search for all of the entities and THEN filtering them out with an expression. That’s a horrible use for LINQ. The OmniQueryProvider creates a query that will only return a result set that conforms to our conditions, which allows us to use LINQ without being idiots.

Feel free to browse the source, try it out, or whatever. I’m not going to go into depth regarding how the custom query provider was created. I’d prefer, instead, to just demonstrate how one can use it. Read on for some examples.

Downloads:

Lastest source of Omniscientist.Cloud.SqlServices

Binaries of Omniscientist.Cloud.SqlServices (not guaranteed to be latest)

Remember that these examples require a reference made to both the Omniscientist.Cloud.SqlServices.dll and Omniscientist.Cloud.SqlServices.Linq.dll assemblies.

The following demonstrates how one can query for all of the containers in an authority:

//Parameterless constructor causes the authority to use the default authority
//defined in settings.
Authority queryAuthority = new Authority();

//Get an instance of our queryable object, the Uri dictates where the query is ran.
OmniQuery<IDataModelLevel> authorityLevel =
    new OmniQuery<IDataModelLevel>(queryAuthority.GetUri());

//Fetch every container
var containers = from container in authorityLevel
                  select container;

foreach(IDataModelLevel container in containers)
{
    //We now have access to each container's id, version, and even their properties.
    //Oh, were you not aware that containers also have properties like entities?
    //They are not flexible, but they are useful, with properties like EntityBytes, etc.
    //They can be accessed via the Body hashtable.
}

You can do some pretty powerful stuff, here’s another example involving the same subject matter:

//Everything is the same up until...

var containers = from container in authorityLevel
                  select new
                    {
                    container.Id,
                    EntityCount = container.Body["EntityCount"],
                    EntitySize = container.Body["EntityBytes"]
                    };

foreach (var container in containers)
{
    Console.WriteLine(
        String.Format(
            "Container Id: {0}    Number of entities: {1}    Size of entities: {2}",
            container.Id,
            container.EntityCount,
            container.EntitySize));
}

Instead of getting data model level instances, we were able to receive an anonymous type instead. The “EntityBytes” and “EntityCount” keys used with the Body hashtable are examples of a container’s non-flexible properties when looking at them from an authority.

The following demonstrates searching for some entities in a container with some conditions applied. The container used here is the same container used for the CloudPhotos demonstrative application I wrote earlier.

Container cloudPhotos = new Container("cloudphotos");

OmniQuery<IDataModelLevel> containerLevel =
    new OmniQuery<IDataModelLevel>(cloudPhotos.GetUri());

//Fetch all entities that have a flexible property "Name" equal to "Omni GUID".
//This pulls in the picture I have in the CloudPhotos container of the Omni GUID
//application.

var entities = from entity in containerLevel
                where entity.Body["Name"].ToString() == "Omni GUID"
                select entity;

//If we know the Id, we can pair up the Id metadata property instead.
var versions = from entity in containerLevel
                where entity.Id == omniGuidId
                select entity.Version;

The above examples will leave you with a collection of entities and a collection of versions that fall under the conditions specified. I only have one Omni GUID picture in the CloudPhotos container, so each query should only return one result.

As I’ve stated before, this is all very new so far, but that doesn’t mean we don’t support BLOB’s! Although you can’t yet get the binary content directly from a LINQ expression (but soon, you will be able to), it’s easily done through an additional step.

BLOB’s are (most likely) mixed with their Entity partners in the same container, so you need to be able to separate the two. Here’s an example of a way to play with BLOB’s by first getting their id’s:

//First, we need to get all the BLOB Id's from their respective paired entities.

//This query will get values for the BlobId flexible property from all entities
//that have non-null value for the BlobId property. This essentially will
//filter out the BLOB's from their paired entities. This is important, as simply
//querying for a BLOB won't let us actually get the binary content.
Container cloudPhotos = new Container("cloudphotos");

OmniQuery<IDataModelLevel> containerLevel =
    new OmniQuery<IDataModelLevel>(cloudPhotos.GetUri());

var blobIds =    from entity in containerLevel
                  where entity.Body["BlobId"] != null
                  select entity.Body["BlobId"].ToString();

RestClient client = new RestClient {Container = cloudPhotos};

foreach (string blobId in blobIds)
{
     //Now that we have the BLOB Id's, we can get the BLOBs' data.
    Blob fatBlob = client.GetBlob(blobId);

    //And voila! You now have a Blob with its binary data stream ready to please.
    byte[] theGoods = fatBlob.ReadBlobStream();
}

Support for externally defined Entity-like types will be coming soon.

An interesting thing I learned about during this endeavour was the verbosity required in creating a custom IQueryProvider. Because of this verbosity, there are probably many types of expressions not yet handled correctly. I’d appreciate any feedback, but I do intend on filling any holes that are currently present.

Also, you should expect really exotic (or maybe not so much) expressions to fail, even something as simple as Contains(). Azure SDS syntax does not allow for much other than “where A == B”; there are no wildcards (that I know of), or anything like the LIKE keyword. If I am mistaken, please let me know!

Well, with this library well on its way, the SQL Data Services on the Cloud is almost getting to the point of being usable.

Until next time.