OakLeaf Systems: Querying Microsoft’s Codename “Social Analytics” OData Feeds with LINQPad

Saturday, November 05, 2011

Querying Microsoft’s Codename “Social Analytics” OData Feeds with LINQPad

My Problems Browsing Codename “Social Analytics” Collections with Popular OData Browsers post of 11/4/2011 described issues that occurred while browsing or attempting to browse the Codename “Social Analytics” project’s OData collections with three free OData browsers. As I noted in that post, browsing large datasets is one key to implementing the Holy Grail of “Agile BigData.”

On 11/5/2011, I received this tweet from Richard Orr of the Social Analytics Team:

Microsoft Connect’s Codename “Social Analytics” page provides cryptic instructions for using LINQPad to access the Social Analytics API. None of the browsers I tested behaved as expected when attempting to execute OData query operations, so I decided to demonstrate using LINQPad, which I had used extensively while writing Professional ADO.NET 3.5 with LINQ and the Entity Framework for Wiley/Wrox in 2008, to process OData queries. Following are the steps I took:

Downloading, Installing and Setting Up LINQPad with the Windows8 Social Analytics Dataset

1. Download and install LINQPad v4.31.0 if you have .NET Framework 2010 installed, otherwise LINQPad v2.31.0 from the LINQPad download page.

2. Launch LINQPad, click the Add Connection button to open the Choose Data Context dialog, and select Microsoft DataMarket Service:

3. Click Next to open the DataMarket Connection dialog, type https://api.datamarket.azure.com/Vancouver/VancouverWindows8/ in the DataMarket Service URI, your account key in the Account Key, and VancouverWindows8 in the Friendly Name text boxes, and verify that the Remember This Connection checkbox is marked:

4. Click Test to check the connection, click OK to dismiss the Connection Successful message and return to the DataMarket Connection dialog.

5. Click OK to close the DataMarket Connection dialog and display a VancouverWindows8 node in the navigation pane. Expand the node to display the collections list:

6. Expand the ContentItems node, open the Databases list, choose VancouverWindows8, and scroll down to display many-to-one () and one-to-many () navigation property links:

7. Test navigation by clicking CalculatedTone property (one) to activate the Tones collection (many), then click the ChildContentItems property (many) to expand the ContentItems node (one).

Executing Simple LINQ Queries

8. Type a collection name (ContentItems for this example) in the query pane and press F5 or click the Execute button to retrieve a default 500 items and display them in a grid:

9. Click the SQL button to display the complete OData query syntax in the results pane:

10. Click the IL (Intermediate Language) button to display the .NET Common Intermediate Language (CIL) instructions for the query:

11. Click the Clear button to remove the tab and right-click a collection item (ContentItems for this example) to display a list of LINQ expression and query templates:

12. Select the ContentItems.Take (50) LINQ expression to display the first 50 items with a ContentItems.Take (50) expression.

13. To page items, insert a Skip (50) expression after ContentItems. and execute the expression:

Executing More Complex LINQ Queries

14. To specify display of fewer columns, select the LINQ from c in ContentItems where … select new { <all columns> } template to add the partial LINQ query shown here:

15. To customize the query, remove the columns you don’t want to display, add a where clause filter, and specify a sort order:

The complete expression syntax for the above result is:

from c in ContentItems
where c.CalculatedToneId == 3 orderby c.PublishedOn descending
select new
{
    c.Id,
    c.Title,
    c.PublishedOn,
    c.CalculatedToneId,
    c.ToneReliability,
    c.LastUpdatedOn,
    c.SiteId,
    c.FeedId,
}

16. To specify the number of items to return or perform paging, enclose the query in parenthesis and append the expression, as in:

(from c in ContentItems
where c.CalculatedToneId == 3 orderby c.PublishedOn descending
select new
{
    c.Id,
    c.Title,
    c.PublishedOn,
    c.CalculatedToneId,
    c.ToneReliability,
    c.LastUpdatedOn,
    c.SiteId,
    c.FeedId,
}).Take (2)

Notice that the first two items aren’t the same as in step 15 because the “Ingestion Engine” has imported several new items in the intervening 5 minutes. Also observe the total value in the ToneReliability column’s last row and the ToolTip with the Total and Average values for the column.