As I was saying last week month year in this post, JOINs in SQL Data Services are not the same as they are in SQL Server. I'd recommend going back to review that post but the upshot of it is that SDS processes JOINs differently.

Using a JOIN in SQL Server can have two possible uses. The first is to bring data together, so that columns from one table appear seamlessly with another table in the result set; the second is to filter data, the only JOIN that SDS supports if you use their JOIN command. But that doesn't mean that you can't JOIN data seamlessly between data sets, you just need to use Extensible Stylesheet Language Transformations (XSLT).

XSLT is a way to manipulate XML data, using an XSLT Engine to transform XML into something else, in this case, an XHTML document. If you look at the generated XML (zip file here), you can see that it alternates between a web site entry and then the calendar entries for that web site. One way to process the XML would be to walk the XML in code and generate output. But the XSLT Engine handles that for you, all we need to do is to tell the engine how to process the data. Below is the pertinent part of the XSLT:

<xsl:for-each select="//WebSite">
   <xsl:sort select="WebSiteID" />
   <xsl:variable name="kWebSiteID" select="WebSiteID"/>
            <xsl:for-each select="//CalendarEntry[WebSiteID=$kWebSiteID]">
              <xsl:sort select="EventDate" />
            </xsl:for-each>
</xsl:for-each>

On the plus side, the engine handles the transformation from XML into XHTML with just a few lines of code:

string rawXML = calender.GetData("calendar-of-events", SDSQuery.ToString());
XPathDocument doc = new XPathDocument(new StringReader(rawXML));
XslTransform xslt = new XslTransform();
MemoryStream strm = new MemoryStream();
XmlTextReader xrdr = new XmlTextReader("SDS_JoinExample.xslt");// (new StringReader(xsltTransformation));
xslt.Load(xrdr, null, null);
// Transform the document to the Writer
xslt.Transform(doc, null, strm);
strm.Position = 0;
string Output = new StreamReader(strm).ReadToEnd();

I've got a class, Events, that handles the call to the SDS and returns XML. We need to load that XML into an XPathDocument. Then the XSLT Transform and finally execute the Transform. Those few lines of code can off load a great deal of work. That's a good thing, in general, but it does highlight the problem I have with things like SDS: there's too much work being done elsewhere. I'll grant this sounds like a shell game since, whether the data gets merged at the data service or on the web server or the client, the same amount of work needs to be done; or does it? When SQL Server provides a JOINed result set, that data can be cached and returned for subsequent calls. If the web server makes the join and caches it, then the cache can be reused for clients it's serving but multiple web servers in a web farm will require multiple database calls. Finally, if the client handles the join then every call is probably going to need to hit the database.

Cloud computing is good and can be beneficial, but you should consider all of the costs involved before jumping feet-first into it.

Download the sample code here.