In SQL Server Data Services, the scope of a query is bound to a Container, but in LitwareHR we had a requirement of searching entities across multiple tenants, and because in our implementation each tenant gets its own Container, we had to create a way of performing queries (the same query to be more precise) that spanned across Containers.

Remember, LitwareHR actually "owns" all containers. LitwareHR's metadata (which is simply another Container as explained in a previous post), contains information about the tenant including a pointer to the tenant's Container.

The trivial way of implementing a cross-Container search would be to iterate on all tenant containers and query each of them. Something like this:

foreach (Container c in TenantContainerCollection)
    IEnumerable<Entity> result = proxy.Query(CreateScope(c.ContainerId), query);
return CombinedResults;


Naturally, this doesn't scale very well and is not taking advantage of the fact that this is a highly parallel problem. In fact, most probably each Query sent to SSDS will end up in a different node of the SSDS fabric, minimizing chances of server side contention.

So we created a helper class: CrossTenantSearch, that essentially takes an array of TenantId's, a query statement (SLINQ) and returns the combined result sets.

Internally, it will asynchronously launch several concurrent searches, each one on a different thread of a ThreadPool and then will wait for all of them to complete, merge the partial results into a single collection and return.

The following diagram illustrates an example of running 3 concurrent queries (with different colors to identify each thread). Notice how the red thread returns even before the 3rd thread is launched, the green thread is longer (potentially returning a lot of results) and the yellow is very short.



After all of them return, results are combined into a single result set and sent back to the requestor. With this approach, the max time for a query is approximately the same as the longest query you have (e.g. the green thread in the diagram above). Caveat: this might not be valid for very large sets of containers, that is when there's a large number of containers you are searching in, because you might get contention issues on the client side and also the merging of all the results might take a lot of time.

There is of course room for improvements: paging, returning to the client partial results and allow the client to drive further fetches, etc. None of these have been implemented in LitwareHR and are left as an exercise to the reader.

Side note: when developing this feature we were puzzled by an exception we got related to thread apartments. It turns out by default, Visual Studio test runner runs in STA. There's a somewhat obscure configuration parameter that you need to add to the testrunconfig file manually, as it is not exposed in the configuration window:

<ExecutionThread apartmentState="MTA"/>

See here for more info.